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

Excel with Microsoft 365 and IONOS!
The Office you know, only better
  • Up to 50 GB Exchange email account
  • Outlook Web App and collaboration tools
  • Expert support & setup service

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.

Note

Excel also offers HLOOKUP and VLOOKUP. While the first function searches horizontally, the second function searches vertically. With the LOOKUP function, you can choose if you want to search rows or columns.

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.

Tip

If the value that is being searched for is smaller than the smallest value in the search vector, LOOKUP will produce an error, since only the next smallest entry can be selected if the search is unable to find an exact match.

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)
Image: LOOKUP in Excel: Example of a search formula
You can create a simple search formula using the Excel LOOKUP function.

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))
Image: LOOKUP in Excel: Combining LOOKUP with SUM
Carry over the LOOKUP result directly into the SUM function.

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)
Image: LOOKUP in Excel: Combining LOOKUP with CONCATENATE
You can provide the search value using the CONCATENATE function.

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)
Image: LOOKUP in Excel: Combining LOOKUP with MATCH
LOOKUP searches for the value that comes closest to the search value, while MATCH displays where it’s located in the list.
HiDrive Cloud Storage
Store and share your data on the go
  • Store, share, and edit data easily
  • Backed up and highly secure
  • Sync with all devices
Was this article helpful?
Go to Main Menu