Excel search function with example formulas (2023)

The tutorial explains the array and vector formats of Excel's LOOKUP function and shows common, non-trivial uses of Excel's LOOKUP function with example formulas.

One of the most common questions that every Excel user asks from time to time is: "How to lookup a value in one sheet and get the corresponding value in another sheet?Of course, there can be many variations on the basic scenario: you can search for the closest match, not the exact match, you can search vertically in a column or horizontally in a row, evaluate one or more criteria, etc. But the bottom line is the same - you need to know how to search in Excel.

Microsoft Excel provides many different ways to search. First, let's learn a function designed to handle the simplest cases of vertical and horizontal searches. As you can easily guess, we are talking about the SEARCH function.

SEARCH function in Excel - syntax and uses

At the most basic level, the LOOKUP function in Excel looks up a value in a column or row and returns a matching value from the same position in another column or row.

There are two ways to SEARCH in Excel:VectorIEducation. Each form is explained separately below.

Excel SEARCH function - vector form

In this context, avectorrefers to a range of one column or one row. Therefore, it uses the vector form of the LOOKUP function to find a specific value in a row or column of data and retrieve the value from the same position in another row or column.

The syntax of the search vector is as follows:

LOOKUP(lookup_value, lookup_vector, [result_vector])

Where:

lookup value(required) - value to search for. It can be a number, text, TRUE or FALSE, or a reference to the cell that contains the value you're looking for.
search_vector(required) - range of one row or column to search. must be correctedincreasing.
results_vector(optional) - a range of a row or column from which you want to get a result - a value in the same position as the value you searched for.results_vectorit must beSame sizeAssearch_field. If omitted, z is returnedsearch_vector.

The following examples show how two simple search types work.

Vertical Search Formula - Search within a column

Suppose you have a list of suppliers in column D (D2:D5) and the products they sold in column E (E2:E5). You create a control panel where your users enter the vendor name in B2 and you need a formula that returns the correct product in B3. The task can be easily performed with the following formula:

=WYSZUKAJ(B2;D2:D5;E2:E5)

To better understand the arguments, take a look at this screenshot:

Horizontal search type: search within a range of one row

If the source data is horizontal, meaning the records are in rows rather than columns, then specify a range of one row insearch_vectorIresult_vectorarguments such as:

5 things to know about the vector form of the LOOKUP function in Excel

values insearch_vectormust be put in orderincreasing, that is, from smallest to largest or from A to Z. Otherwise, Excel's lookup formula may return an error or an incorrect result. if you need to searchunclassified items, then use one of themMATCHING RATElubricantCOMPENSATION FIGHT.
search_vectorIresult_vectorit must beone linelubricanta columnsame size range.
The LOOKUP function in Excel isIt is not case sensitiveIt is not case sensitive.
Excel SEARCH works based onfuzzy match. More specifically, the search type first looks for an exact match. If you can't find the exact price you're looking for, searchnext lower value, which is its greatest valuesearch_vectorwhich is less than or equal tolookup value.
For example, if the search value is "5", the formula will search for it first. If it doesn't find "5", it will look for "4". If it doesn't find "4", it will look for "3" and so on.
Andlookup valueEssmallerthat its smallest valuesearch_vector, LOOKUP in Excel returns #N/A error.

Excel SEARCH function - table form

The array form of the LOOKUP function looks for a specific value in the first column or row of an array and retrieves the value from the same location in the last column or row of the array.

The lookup table has 2 arguments, both required:

LOOKUP(lookup_value, array)

Where:

lookup value- value to lookup in table.
Education- the range of cells in which you want to find the value you searched for. The values in the first column or first row of the table (depending on whether you are using a vertical or horizontal search) must be sorted in ascending order. Uppercase and lowercase letters are considered equivalent.

For example, if vendor names are in the first column of the table (column A) and order dates are in the last column of the table (column C), you can use the following formula to find the name and retrieve the matching date:

=BUSCAR(B2;D2:F5)

UseDo not confuse the table format of the LOOKUP function in ExcelExcel table types. Although it works on arrays, the LOOKUP function is still a simple formula that can be executed in the normal way by pressing Enter.

4 things you need to know about the table format of the LOOKUP function in Excel

Andeducationmothermore rows than columnsor the same number of columns and rows, the search type searches the first column (horizontal search).
Andeducationmothermore columns than rows, Excel LOOKUP searches the first row (vertical search).
If the type cannot find the value it is looking for, it uses the methodthe greatest valuein an array less than or equal tolookup value.
AndThe search price is lessfrom the smallest value in the first column or first row of the array (depending on the dimensions of the array), the lookup type returns the #N/A error.

Important note!The functionality of the LOOKUP table form in Excel is limited and we do not recommend using it. You can use insteadVERTICAL SEARCHlubricantBUSCARIfunction which are enhanced versions for vertical and horizontal search, respectively.

How to use the LOOKUP function in Excel - example formulas

While there are more advanced search and match functions in Excel (which is the subject of the next tutorial), SEARCH is useful in many situations, and the following examples illustrate some non-trivial uses. Note that all the formulas below use the formatvector shapeEXCELLENT SEARCH.

Find a value in the last non-blank cell of a column

If you have a dynamically populated column of data, you might want to select the last entry added, i.e. get the last non-blank cell in the column. To do this, use this general formula:

SEARCH(2, 1/(column<>""),column)

In the above formula, all arguments except the column reference are constants. So, to get the last value in a particular column, you just need to provide the corresponding column reference. For example, to extract the value of the last non-blank cell in column A, use the following formula:

=LOOKUP(2,1/(A:A<>""),A:A)

To get the last value of other columns, modify the column references as shown in the following screenshot: the first reference is the column to check for empty or non-empty cells, and the second reference is the column to return a value:

How this formula works

wlookup valueargument, specify 2 or any other number greater than 1 (you'll see why in a moment).

wsearch_vectorargument, you put this expression: 1/(A:A<>"")

First, it performs a binary operation A:A<>"", which compares each cell in column A to an empty string and returns TRUE for empty cells and FALSE for non-empty cells. In the example above, the formula in F2 returns the following array: {TRUE,TRUE,TRUE,TRUE,FALSE...}
Then divide the number 1 by each item in the table above. When TRUE equals 1 and FALSE equals 0, you get a new array consisting of 1 and #DIV/0. errors (divide by the result 0) and this array is used assearch_vector. In this example it is {1;1;1;1;#DIV/0!...}

Now how does the formula return the last non-empty value in the given column?lookup valuedoes not match any itemssearch_vector? The key to understanding this logic is that Excel's LOOKUP function searches with a fuzzy match, that is, when an exact lookup value cannot be found, it matches the next largest value insearch_vectoris less thanlookup value. In our case,lookup valueis 2 and the largest value is wsearch_vectoris 1, so LOOKUP matches the last 1 in the array, which is the last non-blank cell.

wresult_vectorargument, refers to the column from which you want to return a value, and the lookup type will retrieve the value in the same position as the lookup value.

Advice.If you want to receive callsRow numberholding the last value and then use the ROW function to find it. For example:=LOOKUP(2,1/(A:A<>""),ROW(A:A))

Search for a value in the last non-blank cell of a row

If your source data is sorted in rows instead of columns, you can get the value of the last non-blank cell using this formula:

SEARCH(2, 1/(fila<>""),fila)

In fact, this formula is nothing more than a slight modification of the previous formula, except that it uses a row reference instead of a column reference.

For example, to get the value of the last non-blank cell in row 1, use the following formula:

=LOOKUP(2, 1/(1:1<>""), 1:1)

The following screenshot shows the result:

Get the value associated with the last entry in the row

With a little creativitythe above formulait can be easily adapted to solve other similar tasks. For example, it can be used to get the value associated with the last occurrence of a particular value in the sequence. This may sound a bit vague, but the following example will make it easier to understand.

Suppose you have a summary table where column A contains the names of suppliers and the following columns contain data of some sort for each month. In this example, the cell contains "yes" if the salesperson has closed at least one deal in a given month. Our goal is to match the month with the last "yes" entry in the row.

The task can be solved with the following LOOKUP formula:

=SEARCH(2, 1/(B2:H2="yes"), $B$1:$H$1)

The logic of the formula is basically the same as described in the sectionfirst example. The difference is that it uses the equals operator ("=") instead of the "not equals" operator ("<>"), and it works on rows instead of columns.

The following screenshot shows the result:

Search as an alternative to nested IFs

In all the search types we've covered so far,search_vectorIresult_vectorarguments were represented by range references. However, writing the Excel LOOKUP function allows you to specify vectors in the formvertical array constant, which allows the program's functionality to be replicatedIF nestedwith a more compact and readable formula.

Suppose you have a list of abbreviations in column A and you want to replace them with their full names where "C" is "Completed", "D" is "Development" and "T" is "Test". can be achieved with the following nested IF function:

=IF(A2="c", "Ready", IF(A2="d", "Development", IF(A2="t", "Test", "")))

Or using this search type:

=SEARCH(A2;{"c","d","t"},{"Done","Development","Test"})

As shown in the screenshot below, both types give the same results:

UseFor the Excel lookup formula to work correctly, the values in thesearch arraySort from A to Z or from smallest to largest.

If you are getting values from a lookup table, you can embed the Vlookup function in a filelookup valueargument to match.

Assuming the lookup value is in cell E2, the lookup table is A2:C7 and the column of interest ("Status") is 3^I+Dcolumn in the lookup table, the following formula will do the job:

= VLOOKUP(E2, $A$2:$C$7, 3, FALSE); {"c"; "d"; "t"}; {"Completed"; "Development"; "Testing"})

As shown below, the formula retrieves the project state from the lookup table and replaces the hash with the matching word:

Advice.If you use Excel 2016 with an Office 365 subscription, you can use itSWITCH functionfor similar purposes.

I hope these examples shed some light on how the SEARCH function works. To better understand the formulas, you can download themexamples of search in excel. In the next tutorial, we'll cover other ways to search in Excel and explain which type of search is best for your situation. Thanks for reading and we hope to see you on our blog next week!