How to Use Excel's LOOKUP Function to Find Information

Find data more easily in Excel

By
Ted French
Ted French
Writer
Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.
lifewire's editorial guidelines
Updated on September 11, 2020

The Excel LOOKUP function has two forms: the Vector Form and the Array Form. The Array Form of the LOOKUP function is similar to other Excel lookup functions such as VLOOKUP and HLOOKUP. You can use it to find or look up specific values located in a table of data.

LOOKUP vs. VLOOKUP and HLOOKUP

How it differs is that:

  • With VLOOKUP and HLOOKUP, you can choose which column or row to return a data value from. LOOKUP always returns a value from the last row or column in the array.
  • When finding a match for the specified value (the Lookup_value), VLOOKUP only searches the first column of data and HLOOKUP only the first row. The LOOKUP function searches either the first row or column, depending on the shape of the array.

LOOKUP Function and Array Shape

The shape of the array can be either a square (equal number of columns and rows) or a rectangle (unequal number of columns and rows). The shape affects where the LOOKUP function searches for data:

  • If an array is square or if it is a tall rectangle (taller than it is wide), LOOKUP assumes that the data is arranged in columns and searches for a match to the Lookup_value in the first column of the array.
  • If an array is a wide rectangle (wider than it is tall), LOOKUP assumes that the data is arranged in rows and searches for a match to the Lookup_value in the first row of the array.

The LOOKUP Function Syntax and Arguments: Array Form

The syntax for the Array Form of the LOOKUP function is: 

= LOOKUP(Lookup_value, Array)

Lookup_value (required): A value that the function searches for in the array. The Lookup_value can be a number, text, a logical value, or a name or cell reference that refers to a value.

Array (required): Range cells that the function searches to find the Lookup_value. The data can be text, numbers, or logical values.

Example Using the Array Form of the LOOKUP Function

This example uses the Array Form of the LOOKUP function to find the price of a Whachamacallit in the inventory list.

The shape of the array is a tall rectangle, and the function returns a value located in the last column of the inventory list.

To follow along with this example, enter the data shown in the sample worksheet below.

Sorted data in Excel worksheet

Sort the Data

You must sort the data in the array in ascending order so that the LOOKUP function works properly. When sorting data in Excel, select the columns and rows of data to sort first, which typically includes the column headings.

  1. Highlight cells A4 to C10 in the worksheet.

    Selected cell range in Excel

  2. On the ribbon, go to the Data tab.

    The Data tab in Excel

  3. In the Sort & Filter group, select Sort to open the Sort dialog box.

    The Sort command in Excel

  4. Under the Column heading, select the drop-down menu and choose to sort by Part.

    The "Sort by Part" option in Excel

  5. Under the Sort on heading, select the drop-down menu and choose Cell Values.

    The "Sort on Cell Values" option

  6. Under the Order heading, select the drop-down menu and choose A to Z.

    The "A to Z" order setting

  7. Select OK to sort the data and close the dialog box.

    Sorted data in Excel worksheet

LOOKUP Function Example

Although it is possible to type the LOOKUP function, =LOOKUP(A2,A5:C10), into a worksheet cell, you might find it less confusing to use the function's dialog box. The dialog box lets you enter each argument on a separate line without worrying about the function's syntax, such as parenthesis and the comma separators between arguments.

The steps below detail how the LOOKUP function was entered into cell B2 using the dialog box.

If the function cannot find an exact match for the Lookup_value, it chooses the largest value in the Array that is less than or equal in value to the Lookup_value. If the Lookup_value is missing or smaller than all values in the Array, the LOOKUP function returns an #N/A error.

  1. In the worksheet, select cell B2 to make it the active cell.

    B2 selected in worksheet

  2. Go to the Formulas tab.

    The Formulas tab in Excel

  3. Choose Lookup and Reference to open the function's drop-down list.

    The Lookup & Reference section in the Formulas tab

  4. Select LOOKUP to display the Select Arguments dialog box.

    Select arguments dialog box

  5. Choose lookup_value, array, and select OK to display the Function Arguments dialog box.

    Selected argument in LOOKUP

  6. In the dialog box, select the Lookup_value text box.

    Lookup_value line in dialog box.

  7. In the worksheet, select cell A2 to enter that cell reference into the dialog box.

    Cell A2 selected

  8. In the dialog box, select the Array text box.

    Array line in dialog box.

  9. In the worksheet, highlight cells A5 to C10 to enter this range into the dialog box. This range contains the data to be searched by the function.

    A5 to C10 selected for Array

  10. Select OK to complete the function and close the dialog box.

  11. An #N/A error appears in cell B2 because you need to type a part name in cell A2.

    #N/A error in B2

Enter a Lookup Value

Here's how to enter a name to find the price of an item:

  1. Select cell A2, type Whachamacallit, and press the Enter key on the keyboard.

    Whachamacallit in A2

  2. The value $23.56 appears in cell B2. This is the price of a Whachamacallit located in the last column of the data table.

  3. Test the function by typing other part names into cell A2. The price for each part in the list appears in cell B2.

  4. When you select cell B2 the complete function =LOOKUP(A2,A5:C10) appears in the formula bar above the worksheet.

Was this page helpful?