Look up values in a list of data

gray laptop computer showing html codes in shallow focus photography
  • Applies to: Excel for Microsoft 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007. 

You can look up values in a list of data for several reasons, one of which will be described with this example. Let’s imagine that you aim to look up an employee’s phone extension by using their badge number, or the right commission rate for a given sales amount. You look up data to swiftly and conveniently locate certain data in a list and to automatically confirm that you are employing accurate data. After you look up the data, you can run calculations or present results with the values returned. There are multiple ways to look up values in a list of data and to illustrate the results.

Look up values in a list of data – What do you want to do?

(Vertically) look up values in a list by using an exact match

Using an approximate match – Look up values vertically in a list

Employing an exact match to look up values vertically in a list of unknown size

Applying an exact match – Look up values horizontally in a list

Look up values horizontally in a list by using an approximate match

Create a lookup formula with the Lookup Wizard (Excel 2007 only)

(Vertically) look up values in a list by using an exact match

You can complete this task by using either the VLOOKUP function, or a mixture of the INDEX and MATCH functions.

VLOOKUP examples

VLOOKUP Example 1 - Look up values in a list of data.
VLOOKUP Example 2 - Look up values in a list of data.

For further information, see VLOOKUP function.

INDEX and MATCH examples

INDEX and MATCH functions can be used as a replacement to VLOOKUP to look up values in a list of data.

In basic English it means:

=INDEX(I want the return value from C2:C10, that will MATCH(Kale, which is somewhere in the B2:B10 array, where the return value is the first value corresponding to Kale))

The formula checks for the first value in C2:C10 that matches with Kale (in B7) and returns the value in C7 (100). In this case, this is the first value that matches Kale.

For more information, see INDEX function and MATCH function.

Top of Page

Using an approximate match – Look up values vertically in a list

To do this, use the VLOOKUP function.

Important: Guarantee that the values in the first row have been arranged by an ascending order.

An example of VLOOKUP formula looking for an approximate match

In the above example, VLOOKUP looks for the first name of the student who has 6 tardies in the A2:B7 range. Since there is no entry for 6 tardies in the table, VLOOKUP looks for the next highest match lower than 6. It then captures the value 5, linked to the first name Dave. It therefore returns Dave.

For more information, see VLOOKUP function.

Top of Page

Employing an exact match to look up values vertically in a list of unknown size

For this task, you will need to use the OFFSET and MATCH functions.

Note: Use this approach anytime your data is in an external data range that you refresh each day. You know the price is in column B, but you are unsure about how many rows of data the server will return, and the first column isn’t sorted alphabetically.

An example of OFFSET and MATCH functions

C1 is the upper left cells of the range (also called the starting cell).

MATCH(“Oranges”,C2:C7,0) looks for Oranges in the C2:C7 range. However, you should avoid taking into account the starting cell in the range.

1 is the number of columns to the right of the starting cell where the return value should originate from. In our example, the return value is from column D, Sales.

Top of Page

Applying an exact match – Look up values horizontally in a list

You will need to use the HLOOKUP function to complete this task. Refer to the example below:

An example of HLOOKUP formula looking for an exact match

HLOOKUP looks up the Sales column, and returns the value from row 5 in the confirmed range.

For further information, see HLOOKUP function.

Top of Page

Look up values horizontally in a list by using an approximate match

You will want to use the HLOOKUP function in order to complete this task.

Important:  Above all, make sure the values in the first row have been sorted in an ascending order.

An example of HLOOKUP formula looking for an approximate match

In the above example, HLOOKUP looks for the value 11000 in row 3 in the defined range. It does not find 11000 and hence looks for the next largest value less than 1100 and returns 10543.

For more information, see HLOOKUP function.

Top of Page

Create a lookup formula with the Lookup Wizard (Excel 2007 only)

Note: The Lookup Wizard add-in was discontinued in Excel 2010. Since then, this functionality has been updated by the function wizard and the available Lookup and reference functions (reference).

In Excel 2007, the Lookup Wizard produces the lookup formula associated with a worksheet’s data including row and column labels. The Lookup Wizard aids you in tracking down other values in a row when you’re certain of the value in one column, and vice versa. The Lookup Wizard deploys INDEX and MATCH in the formulas that it designs.

  1. Firstly, click a cell in the range.
  2. Secondly, click Lookup on the Formulas tab. This is found in the Solutions group.
  3. Next, if the Lookup command is missing, then you must load the Lookup Wizard add-in programme. How to load the Lookup Wizard Add-in programme
  4. Begin by going to select the Microsoft Office Button Office button image and press Excel Options. Then, choose the Add-ins category.
  5. Moreover, in the Manage box, press Excel Add-ins. Then, select Go.
  6. Also, in the Add-Ins available dialogue box, click the checkbox beside Lookup Wizard. Next, choose OK.
  7. Finally, follow the instructions in the wizard.

Top of Page

Leave a Reply

%d bloggers like this: