Excel LOOKUP formula: Commands and examples

Alongside its statistical and algebraic functions, Excel offers lookup and reference functions, with the LOOKUP function being especially useful.

What is Excel LOOKUP used for?

Let’s imagine you have a table with two or more columns. For each entry in the first column, there’s a corresponding value in the second column in the same row. If you want to find out the value for a certain entry, you can either manually scroll down the rows or you can search for it with the LOOKUP function. Enter the search term into the formula and Excel will give you the corresponding value. Alternatively, you can also create a formula with a search field. You can then easily integrate the cell with the search term into the LOOKUP formula.

While this direct application of the function is useful, combining LOOKUP with other functions is even more effective.

What is the syntax for Excel LOOKUP?

LOOKUP can be used in Excel in two ways: as a vector function or as an array function. Using it as a vector function is probably more popular though. The syntax shown here works in Excel versions from Office 2016 onwards as well as Microsoft 365.

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value: Enter the content you want to search for here, for example numerical values, text (in quotation marks), Boolean values, cell references or a formula.
• lookup_vector: This term defines where the function should search for the value entered in the first parameter. In most cases, you’ll enter a cell range here. But you can also input the values directly in the form of an array (in curly brackets) into the parameter.
• result_vector: This optional parameter allows you to set the range where a corresponding value can be found. It’s important that this parameter is the same size as the search vector.

With the vector variant, you can only enter a row or column for the search or result range. The two vectors, however, don’t need to be located directly next to each other. As long as both parameters are the same size, the two ranges can be found in completely different places in the spreadsheet.

The search function is always approximate. This means the search criterion doesn’t have to be an exact match with one of the values in the search vector. Excel will then try to find the next smallest value. This only works though if you sort the list in ascending order beforehand. If the table is mixed up, the function will provide incorrect results.

Excel LOOKUP example

LOOKUP is easiest to use as a search formula. It enables you to search through an entire column, for example, and find a corresponding value. In the example below, we’re going to use an inventory list. Each item has its own ID and we’re going to search for the stock quantity of the items listed.

=LOOKUP($A$12, A2:A10, B2:B10)

Enter the inventory number into the search field and the LOOKUP function will display the number of items for the inventory number. You can now dynamically search for each item.

The LOOKUP function can also effectively be combined with other Excel functions. For instance, you can add the results of a LOOKUP or multiple LOOKUPs together.

=SUM(LOOKUP($A$12, A2:A10, B2:B10), LOOKUP($B$12, A2:A10, B2:B10))

The formula adds the stock quantity for the two items together.

It’s also possible to enter the search criterion using a formula. In our example, the item IDs are made up of a letter and a number. You can enter these two elements separately and then combine them with the CONCATENATE function. Since the first part of the names only consist of one of three possible letters, creating a drop-down list is worthwhile.

=LOOKUP(CONCATENATE($A$12,$B$12),A2:A10,B2:B10)

You can now use two separate search fields to find out the ID for an item.

Omitting the result vector while performing approximate searches with LOOKUP also allows you to determine which value in a list comes closest to the value you are searching for. When you combine this with the Excel function MATCH, Excel even shows you the row where the value can be found.

=MATCH(LOOKUP(55,A1:A10),A1:A10)