Easily Search for Data with The Excel LOOKUP Function

Young woman with a loupe
alvarez / Getty Images

Excel's LOOKUP function can be helpful in retrieving a single value from a one-row or one-column ranges 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 return a value from the same position in a second row or column.

For this tutorial, we will be utilizing the Vector form of the LOOKUP function.

01
of 02

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 only one row or column that the function searches to find 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 this argument is omitted, the function returns the Lookup_value argument if it is present in the Lookup_vector.

If the function cannot 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.

02
of 02

LOOKUP Vector Function Example

Screenshot of Excel showing the Formula Builder

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)

To simplify entering the function's arguments, the Formula Builder is used in the following steps. 

  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 Formula Builder.
  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 — in this cell, we will type the part name that we are searching.
  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 Done to complete the function.
Screenshot of Excel showing how to select the argument

An #N/A error appears in cell E2 because we have yet to type a part name in cell D2.

Click on cell D2, type the word Gear and press the Enter key on the keyboard. The value $20.21 should appear in cell E2 as this is the price of a gear located in the second column of the data table.

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