Within Excelyou can find Functions of searching and referencing. With them, you can locate within spreadsheet different values according to the criteria you specify in your search.
In this sense, it is a great tool for provide reference information for . Each of these functions has a specific syntax and you can take advantage of them depending on your needs.
Here we will talk about how to use excel information search functions with a detailed step-by-step guide.
What are the search functions and what are they for in my Excel spreadsheets?
With the help of excel search functionsthe user has the possibility to find within a spreadsheet any type of information that requires. To make use of these functions, simply write the formula corresponding to each case.
In this way, there different search and reference functions that can facilitate your work within Excel.
These are:
- ADDRESS: help to return a reference as text within a single cell from a spreadsheet.
- AREAS: sample the number of areas of a reference.
- TO CHOOSE: offers the possibility of choose a value from a list of values.
- COLUMN: shows the column number of a reference.
- COLUMNS: shows the number of columns of a reference.
- FILTER: available in Office 365 and allows filter a databased on the criteria defined by the user.
- FORMULA TEXT: for Office 2013 versions. Returns the formula in the reference given as text.
- IMPORT PIVEDATA: Available in the Office 2010 version. Displays the data stored in a report .
- HLOOKUP: It allows search the top row of an array y returns the value of the specified cell.
- HYPERLINK: help to create a shortcut or jump which opens a document stored within a network server, on the Internet, etc.
- INDEX: choose a value of a reference or an array.
- INDIRECT: sample a reference indicated by a text value.
- SEARCH: allows to get values of a vector or matrix.
- COINCIDE: locate values of a reference or matrix.
- OFFSET: sample a reference offset about a specified reference.
- ROW: sample the row number of a reference.
- ROWS: sample the number of rows of a reference.
- RTDR: It allows recover data in real time from a program compatible with automation COM.
- ORGANIZE: Available in Office 365 and sorts the contents of a range or an array.
- SORT BY: Available in Office 365. Sorts the contents of a range or array according to the values of a corresponding range or matrix.
- TRANSPOSE: shows the transpose of a matrix.
- UNIQUE: Available in Office 365. Returns a list of unique values from a range or list.
- CONSULTATION: It allows search in the first column of an array and moves on horizontal by row, with the goal of returning the value of a cell.
- SEARCHX: available in Office 365. Allows the user to search for a range or array and returns an element corresponding to the first match it finds. It should be mentioned that in case of no match exists, XLOOKUP helps return closest match (approximate).
- XMATCH: Available in Office 365. Shows the relative position of an element in an array or range of cells.
Steps to use the information search functions in Excel correctly
take advantage of information search functions within ExcelIt is easier than it seems to be.
Therefore, we will explain step by step how to do it correctly in each case:
SEARCH function
The use of this function is recommended to users when they need search within a single row or column and find a value from the same position in a second row or column.
Specifically LOOKUP has two options to use, vectorial and matrix-based:
SEARCH vector shape
With the vector shapeyou may search in a range of a row or column (referred to as vector) a certain value. In this way, returns it from the same position but in a second rank.
The corresponding syntax is: LOOKUP(lookup_value, lookup_vector, ).
In addition, it has the following arguments:
- Lookup_Value (Required): this is the value you are looking for with the LOOKUP function inside the first vector. This can be a text, number, logical value, and even a reference name indicating a specific value.
- Comparison_Vector (Required): refers to a range that contains only one row or column. These can be texts, numbers or logical values.
- result_vector (Optional): is a range that contains only one column or row. It needs to be the same size as lookup_vector.
Finally, so that these formulas show the resultsit may be necessary select them in the spreadsheet. To do this, press F2 and then Enter. Other than that, you can adjust column widths to see all the data, in case you require it.
SEARCH in matrix form
With the array form of LOOKUPIt can find the specified value in the first column or row of an array. With it, the value of the same position in the last row or column of the matrix is displayed.
Its use is recommended in case the values you are looking for are located in the first row or column of a matrix.
The syntax for this is: LOOKUP(lookup_value, array).
Apart from that, it has the following arguments:
- Lookup_Value (Required): is the value that the LOOKUP function looks for. It can be a text, number, reference name of a value and even a logical value. In addition to that, you should know that if you can’t find the lookup_value, use the largest value in the array that is less than or equal to the lookup_value. Also, if said value is less than the first row or column (according to the dimensions of the matrix), a #N/A error value will be returned.
- Matrix (Required): corresponds to the range of cells containing numbers, text, or logical values that you want to compare with lookup_value.
FIND and FINDB function
With these functions, the user can search for a text string within a second string. In this way, returns the number of the starting position of the first string starting from the first character of the second.
Specifically FIND has two options to use, FIND and FINDB:
FIND function
Always count each character as 1regardless of whether it is single byte or double byteregardless of the default language setting.
The syntax used in these cases is: (search_text, within_text, ).
FINDB function
takes care of count each double-byte character as 2if the language edition option that admits DBCS. After that, it is established as default language. If not, the function will count each character as 1.
The syntax for FINDRB is: (search_text, within_text, ).
In both cases, the arguments of these functions are:
- Search_text (Required): text you want find.
- Inside_the_text (Required): text containing the text you want find.
- start_num (Optional): helps specify the character from which to start start the search.
Observations What to take into account when using these Excel functions?
All functions that you can play within ExcelThey have a series of very useful recommendations that will allow you to work with them more easily and avoid errors during your jobs.
Some observations to consider with the LOOKUP function are:
- if you can’t find the lookup_value with the SEARCH function, you should know that the function returns the largest value in comparison_vectorwhich is less than or equal to the lookup_value.
- In case the lookup_value is better than the smallest value of the comparison_vectorLOOKUP will return a #N/A error value.
On the other hand, the observations corresponding to FIND and FINDRB are:
- Both are case sensitive. Besides, do not allow the use of wildcards. For this reason, if you do not want to perform a distinct search of this type, use SEARCH and SEARCHB.
- search_text must not contain wildcards.
- In case search_text is “” (empty text)the FIND function will match the first character of the search string. This means the character of start_num or 1.
- If search_text is not in within_textFIND and FINDB will throw the error #VALUE! or #VALUE!.
- If start_num is not greater than zero, the error #VALUE! #VALUE! error.
- If start_num is greater than the length of within_textthe error message will also be displayed #VALUE! error #VALUE!.
- Use start_num to omit a specific number of characters.
Facebook Comments
Disqus