Easily Search for Data With the Excel LOOKUP Function

Magnifying glass on a spreadsheet

tomislz / Getty Images

Excel's LOOKUP function is helpful when you're looking for a single value in a one-row or one-column range of data. The function itself has two forms, an array form and a vector form, which vary in their operation depending upon your needs.

  • Array: This form of LOOKUP looks in the first row or column of an array, a block of data containing multiple rows and columns, for the specified value, and then returns a value from the same position in the last row or column of the array.
  • Vector: This form of LOOKUP searches a vector, a single row or column of data, for a specified value and then returns a value from the same position in a second row or column.

For this tutorial, we are using the Vector form of the LOOKUP function.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

This article is about the LOOKUP function, not VLOOKUP. See How to use Excel's VLOOKUP Function.

LOOKUP Function Syntax and Arguments

Screenshot of Excel showing the LOOKUP function syntax

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. The syntax for the Vector Form of the LOOKUP function is:

=LOOKUP(Lookup_value,Lookup_vector,[Result_vector])

Lookup_value (required): A value that the function searches for in the first vector. The Lookup_value can be a number, text, a logical value, or a name or cell reference that refers to a value. If the argument is smaller than all values in the Lookup_vector, the function will return a #N/A error. Additionally, the vector must be sorted in ascending order (A to Z or smallest to largest for numbers).

Lookup_vector (required): A range containing one row or column where the function searches for the Lookup_value. The data can be text, numbers, or logical values.

Result_vector (optional): A range that contains only one row or column. This argument must be the same size as Lookup_vector. If you omit this argument, the function returns the Lookup_value argument if it is present in the Lookup_vector.

If the function can't find an exact match for the Lookup_value, it chooses the largest value in the Lookup_vector that is less than or equal in value to the Lookup_value.

LOOKUP Vector Function Example

Excel's LOOKUP vector Function Dialog Box.

As seen in the image above, this example will use the Vector Form of the LOOKUP function in a formula to find the price of a Gear in the inventory list using the following formula:

=LOOKUP(D2,D5:D10,E5:E10)

The following steps use Function Dialog Box to input the LOOKUP formula. 

  1. Click on cell E2 in the worksheet to make it the active cell.

  2. Click on the Formulas tab of the ribbon menu.

  3. Choose Lookup and Reference from the ribbon to open the function drop-down.

  4. Click on the LOOKUP in the list to bring up the Function Dialog Box.

    Excel's Select Arguments dialog box for the LOOKUP function.
  5. Click on the lookup_value, lookup_vector, result_vector option in the list. Then click OK.

  6. Click on the Lookup_value line.

  7. Click on cell D2 in the worksheet to enter that cell reference. Later, you'll input the part name you want to search for in Cell D2

  8. Click on the Lookup_vector line.

  9. Highlight cells D5 to D10 in the worksheet to enter this range — this range contains the part names.

  10. Click on the Result_vector line.

  11. Highlight cells E5 to E10 in the worksheet to enter this range — this range contains the prices for the list of parts.

  12. Click OK to complete the function.

An #N/A error appears in cell E2 because cell D2 is blank for now.

Click on cell D2, type the word Doohickey and press the Enter key on the keyboard. The value $20.21 should appear in cell E2 as this is the price of a Doohickey as listed in the table.

Test the function by typing other part names into cell D2, and the corresponding price should appear in cell E2.