Find Information in Data Tables With Excel's LOOKUP Function

01
of 01

Excel LOOKUP Function Tutorial in Array Form

Finding Information with the LOOKUP Function in Excel
Finding Information with the LOOKUP Function in Excel. © Ted French

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 in that it can be used to find or look up specific values located in a table of data.

How it differs is that:

  1. With VLOOKUP and HLOOKUP, you can choose which column or row to return a data value from, while LOOKUP always returns a value from the last row or column in the array.
  2. In trying to find a match for the specified value - known as the Lookup_value - VLOOKUP only searches the first column of data and HLOOKUP only the first row, while the LOOKUP function will search either the first row or column depending on the shape of the array.

LOOKUP Function and Array Shape

The shape of the array - whether it is square (equal number of columns and rows) or a rectangle (unequal number of columns and rows) - affects where the LOOKUP function searches for data:

  • If an array is square in shape or if it is a tall rectangle (taller than it is wide), LOOKUP assumes that the data is arranged in columns and therefore 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 therefore 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.

Notes:

  • For the LOOKUP function to work correctly, the Array argument must be sorted in ascending order (A to Z or smallest to largest for numbers)
  • 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 will return an #N/A error

Example Using the Array Form of the LOOKUP Function

As seen in the image above, this example will use 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. Consequently, the function will return a value located in the last column of the inventory list.

Sorting the Data

As indicated in the notes above, the data in the array must be sorted in ascending order so that the LOOKUP function will work properly.

When sorting data in Excel it is necessary to first select the columns and rows of data to be sorted. Normally this includes the column headings.

  1. Highlight cells A4 to C10 in the worksheet
  2. Click on the Data tab of the ribbon menu
  3. Click on the Sort option in the middle of the ribbon to open the Sort dialog box
  4. Under the Column heading in the dialog box choose to sort by Part from the drop down list options
  5. If necessary, under the Sort on heading choose Values from the drop down list options
  6. If necessary, under the Order heading choose A to Z from the drop down list options
  7. Click OK to sort the data and close the dialog box
  8. The order of data should now match that seen in the image above

LOOKUP Function Example

Although it is possible to just type the LOOKUP function

=LOOKUP(A2,A5:C10) 

into a worksheet cell, many people find it easier 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.

  1. Click on cell B2 in the worksheet to make it the  active cell;
  2. Click on the Formulas tab;
  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, array 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 A2 in the worksheet to enter that cell reference into the dialog box;
  9. Click on the Array line in the dialog box
  10. Highlight cells A5 to C10 in the worksheet to enter this range into the dialog box - this range contains all of the data to be searched by the function
  11. Click OK to complete the function and close the dialog box
  12. An #N/A error appears in cell E2 because we have yet to type a part name in the cell D2

Entering a Lookup Value

  1. Click on cell A2, type Whachamacallit and press the Enter key on the keyboard;
  2. The value $23.56 should appear in cell B2 as 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 will appear in cell B2;
  4. When you click on cell E2 the complete function =LOOKUP(A2,A5:C10) appears in the formula bar above the worksheet.
Format
mla apa chicago
Your Citation
French, Ted. "Find Information in Data Tables With Excel's LOOKUP Function." ThoughtCo, Jul. 31, 2017, thoughtco.com/search-data-tables-with-lookup-function-3124097. French, Ted. (2017, July 31). Find Information in Data Tables With Excel's LOOKUP Function. Retrieved from https://www.thoughtco.com/search-data-tables-with-lookup-function-3124097 French, Ted. "Find Information in Data Tables With Excel's LOOKUP Function." ThoughtCo. https://www.thoughtco.com/search-data-tables-with-lookup-function-3124097 (accessed January 20, 2018).