Use Search Functions in Excel  Step by Step Guide ▷ 2020

Within Excel, you can find Functions of searching and referencing. With them, you can locate within the spreadsheet different values ​​according to the criteria you specify in your search.

In this sense, it is a great tool for output cell reference information. 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’s information search functions with a detailed step-by-step guide.

What are search functions and what are they for in my Excel spreadsheets?

What are search functions and what are they for in my Excel spreadsheets?

With the Excel search functions help, the user has the possibility to find any type of information within a spreadsheet that you require. To use these functions, just write the formula for 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 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: show the column number of a reference.
  • COLUMNS: show the number of columns in a reference.
  • FILTER: available in Office 365 and allows filter a range of data, based on the criteria defined by the user.
  • FORMULATEXT: for Office 2013 versions. Display the formula in the given reference as text.
  • IMPORT DYNAMIC DATA: available in the Office 2010 version. Displays the data stored in a pivot table report.
  • SEARCHH: It allows search the top row of an array and returns the value of the specified cell.
  • HYPERLINK: help to create a shortcut or jump that 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 FOR: 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 in a reference.
  • RDTR: It allows retrieve data in real time from a program compatible with automation COM.
  • ORDER: available in Office 365 and sorts the content of a range or an array.
  • SORT BY: available in Office 365. Sort the content of a range or array according to the values ​​of a corresponding range or matrix.
  • TRANSPOSE: shows the transpose a matrix.
  • ONLY: available in Office 365. Returns a list of unique values ​​from a range or list.
  • CONSULTAV: It allows search in the first column of a matrix and moves in 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 return an element corresponding to the first match it finds. It should be mentioned that in case of no match, XLOOKUP helps to return the closest match (approximate).
  • XMATCH: available in Office 365. Displays the relative position of an element in an array or range of cells.

Steps to use the information search functions in Excel correctly

Steps to use the information search functions in Excel correctly

Take advantage of information search functions within Excel, It is easier than it seems to be.

Therefore, we will explain the step by step how to do it in each case correctly:

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 SEARCH has two options to use, in vector form and in matrix form:

SEARCH in vector shape

With the vector shape, you can search in a range of a row or column (called a vector) a certain value. In this way, returns it from the same position but in a second rank.

The corresponding syntax is: SEARCH (lookup_value, comparison_vector, [vector_resultado]).

Additionally, it has the following arguments:

  • Lookup_Value (Required): this is the value you are looking for with the LOOKUP function within the first vector. This can be a text, number, logical value, and even a reference name that indicates 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, for what these formulas show the results, it may be necessary select them in the spreadsheet. To do this, press F2 and later Enter. Other than that you can adjust column widths to see all the data, in case you require it.

SEARCH for array shape

With the array form of SEARCH, It can find the specified value in the first column or row of an array. With it, the value of the same position is displayed in the last row or column of the matrix.

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: FIND (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 cannot find the search_value, use the largest value in the array that is less than or equal to the search_value. Also, if this value is less than the first row or column (according to the dimensions of the matrix), an error value # N / A will be returned.
  • Matrix (Required): corresponds to the range of cells that contain 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 1, regardless of whether it is from one byte or double byteregardless of the default language setting.

The syntax used in these cases is: (search_text, within_text, [núm_inicial]).

FINDB function

Takes care of count each double-byte character as 2, if the option to edit a language that admits DBCS. After that, it is established as default language. If not, the function will count each character as 1.

The syntax for FINDB is: (search_text, within_text, [núm_inicial]).

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.
  • Initial_Num (Optional): helps to specify the character from which to start the search.

Observations What to keep in mind when using these Excel functions?

Observations What to keep in mind when using these Excel functions?

All functions that you can play within Excel, they have a series of very helpful recommendations that will allow you to work with them more easily and avoid errors during your jobs.

Some observations to consider with the SEARCH 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_vector, which is less than or equal to the lookup_value.
  • In case the lookup_value is better than the smallest value of the comparison_vector, LOOKUP will return an # N / A error value.

On the other hand, the observations corresponding to FIND and FINDB are:

  • Both are case-sensitive. Apart, do not allow the use of wildcard characters. For that reason, if you don’t want to perform a distinguished search of this type, use FIND and FINDB.
  • Search_text must not contain wildcard characters.
  • In case search_text is “” (empty text), the FIND function will match the first character of search string. This means the character of start_num or 1.
  • If the search_text is not in_the_text, FIND and FINDB will throw the error #VALUE! or # VALUE!.
  • If start_num is not greater than zero, the #VALUE! error will be displayed. #VALUE! error.
  • If start_num is greater than the length of within_text, the error message will also be displayed #VALUE! error # VALUE!.
  • Use start_num to skip a specific number characters.

If you have any questions, leave them in the comments, we will answer you as soon as possible, and it will also be of great help to more members of the community. Thank you! 😉

You may be interested:

Rate this post

Leave a Comment