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.

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.

  • Highlight cells A4 to C10 in the worksheet.
  • On the ribbon, go to the Data tab.
  • In the Sort & Filter group, select Sort to open the Sort dialog box.
  • Under the Column heading, select the drop-down menu and choose to sort by Part.
  • Under the Sort on heading, select the drop-down menu and choose Cell Values.
  • Under the Order heading, select the drop-down menu and choose A to Z.
  • Select OK to sort the data and close the dialog box.

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.

Highlight cells A4 to C10 in the worksheet.

On the ribbon, go to the Data tab.

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

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

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

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

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

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

  • In the worksheet, select cell B2 to make it the active cell.
  • Go to the Formulas tab.
  • Choose Lookup and Reference to open the function’s drop-down list.
  • Select LOOKUP to display the Select Arguments dialog box.
  • Choose lookup_value, array, and select OK to display the Function Arguments dialog box.
  • In the dialog box, select the Lookup_value text box.
  • In the worksheet, select cell A2 to enter that cell reference into the dialog box.
  • In the dialog box, select the Array text box.
  • 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.
  • Select OK to complete the function and close the dialog box.
  • An #N/A error appears in cell B2 because you need to type a part name in cell A2.

Enter a Lookup Value

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

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.

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

Go to the Formulas tab.

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

Select LOOKUP to display the Select Arguments dialog box.

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

In the dialog box, select the Lookup_value text box.

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

In the dialog box, select the Array text box.

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.

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

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

  • Select cell A2, type Whachamacallit, and press the Enter key on the keyboard.
  • The value $23.56 appears in cell B2. This is the price of a Whachamacallit located in the last column of the data table.
  • Test the function by typing other part names into cell A2. The price for each part in the list appears in cell B2.
  • When you select cell B2 the complete function =LOOKUP(A2,A5:C10) appears in the formula bar above the worksheet.

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

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

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

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

Get the Latest Tech News Delivered Every Day