Search for Data with Excel LOOKUP Function

 Use Excel's LOOKUP function -- vector form -- to retrieve a single value from a one-row or one-column range of data. Learn how with this step by step guide.

01
of 04

Find Data in Columns or Rows with Excel's LOOKUP Function

Find Specific Information with Excel's LOOKUP Function - Vector Form
Find Specific Information with Excel's LOOKUP Function - Vector Form. © Ted French

Excel's LOOKUP function has two forms: 

How they differs is that: 

  • The Array form 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.
  • The Vector Form 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.
02
of 04

The LOOKUP Function Syntax and Arguments - Vector Form

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.

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.

Notes:

  • For the LOOKUP function to work correctly, the Lookup_vector must be sorted in ascending order (A to Z or smallest to largest for numbers)
  • If the Result_vector 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;
  • If the Lookup_value is smaller than all values in the Lookup_vector, the LOOKUP function will return a #N/A error.
03
of 04

LOOKUP Function Example

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 LOOKUP function dialog box 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 list;
  4. Click on the LOOKUP in the list to bring up the Select arguments dialog box;
  5. Click on the lookup_value, lookup_vector, result_vector option in the list;
  6. Click OK to bring up the Function Arguments dialog box;
  7. In the dialog box, click on the Lookup_value line;
  8. Click on cell D2 in the worksheet to enter that cell reference into the dialog box - in this cell we will type the part name that we are searching for
  9. Click on the Lookup_vector line in the dialog box;
  10. Highlight cells D5 to D10 in the worksheet to enter this range in the dialog box - this range contains the part names;
  11. Click on the Result_vector line in the dialog box;
  12. Highlight cells E5 to E10 in the worksheet to enter this range in the dialog box - this range contains the prices for the list of parts;
  13. Click OK to complete the function and close the dialog box;
  14. An #N/A error appears in cell E2 because we have yet to type a part name in cell D2
04
of 04

Entering a Lookup Value

Click on cell D2, type Gear and press the Enter key on the keyboard

  1. The value $20.21 should appear in cell E2 as this is the price of a gear located in second column of the data table;
  2. Test the function by typing other part names into cell D2. The price for each part in the list will appear in cell E2;
  3. When you click on cell E2, the complete function
     =LOOKUP (D2, D5:D10, E5:E10) appears in the formula bar above the worksheet.
Format
mla apa chicago
Your Citation
French, Ted. "Search for Data with Excel LOOKUP Function." ThoughtCo, Jan. 19, 2018, thoughtco.com/find-data-with-lookup-function-3124096. French, Ted. (2018, January 19). Search for Data with Excel LOOKUP Function. Retrieved from https://www.thoughtco.com/find-data-with-lookup-function-3124096 French, Ted. "Search for Data with Excel LOOKUP Function." ThoughtCo. https://www.thoughtco.com/find-data-with-lookup-function-3124096 (accessed January 22, 2018).