Look up values in a list of data

Let's say that you want to look up an employee's phone extension by using their badge number, or the correct rate of a commission for a sales amount. You look up data to quickly and efficiently find specific data in a list and to automatically verify that you are using correct data. After you look up the data, you can perform calculations or display results with the values returned. There are several ways to look up values in a list of data and to display the results.

What do you want to do?


Look up values vertically in a list by using an exact match

To do this task, you can use the VLOOKUP function, or a combination of the INDEX and MATCH functions.

Example that uses the VLOOKUP Function

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow do I copy an example? Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.
  4. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Important    Do not select the row or column headers.

  1. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

 
1
2
3
4
5
6
7
8


9
A B C D
Badge Number Last Name First Name Extension
ID-34567 Davolio Nancy 5467
ID-16782 Fuller Andrew 3457
ID-4537 Leverling Janet 3355
ID-1873 Peacock Margaret 5176
ID-3456 Buchanan Steven 3453
ID-5678 Suyama Michael 428
Formula Description (Result)
=VLOOKUP("ID-4537", A1:D7, 4, FALSE) Lookup the badge number, ID-4537, in the first column and return the matching value in the same row of the fourth column (3355)

For more information about how to use this function, see VLOOKUP function.

Example that uses the INDEX and MATCH function

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.
  4. Important For the example to work properly, you must paste it into cell A1 of the worksheet.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
  6. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

 
1
2
3
4
5
6

7
A B
Product Count
Bananas 38
Oranges 25
Apples 41
Pears 40
Formula Description (Result)
=INDEX(A2:B5,MATCH("Pears",A2:A5,0),2) Looks up Pears in column A and returns the value for Pears in column B (40).

The formula uses the following arguments.


Formula to look up a value in an unsorted list

Formula to look up a value in an unsorted range (INDEX function)
Callout 1 A2:B5: The entire range in which you are looking up values.
Callout 2 MATCH("Pears",A2:A5,0): The MATCH function determines the row number.
Callout 3 "Pears": The value to find in the lookup column.
Callout 4 A2:A5: The column for the MATCH function to search.
Callout 5 2: The column from which to return the value. The leftmost column is 1.

For more information about how to use these functions, see INDEX function and MATCH function.

Top of Page Top of Page

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

To do this task, use the VLOOKUP function.

 Important   This method only works if the values in the first column have been sorted in ascending order.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time. Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.
  4. Important For the example to work properly, you must paste it into cell A1 of the worksheet.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
  6. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

In this example, you know the frequency and want to look up the associated color.

 
1
2
3
4
5
6
7



8
A B
Frequency Color
4.14 red
4.19 orange
5.17 yellow
5.77 green
6.39 blue
Formula Description (Result)
=VLOOKUP(5.93, A1:B6, 2, TRUE) Looks up 5.93 in column A, finds the next largest value that is less than 5.93, which is 5.77, and then returns the value from column B that's in the same row as 5.77 (green)

For more information about how to use this function, see VLOOKUP function.

Top of Page Top of Page

Look up values vertically in a list of unknown size by using an exact match

To do this task, use the OFFSET and MATCH functions.

Use this approach when your data is in an external data range that you refresh each day. You know the price is in column B, but you don't know how many rows of data the server will return, and the first column isn't sorted alphabetically.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.

Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.
  4. Important For the example to work properly, you must paste it into cell A1 of the worksheet.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
  6. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

 
1
2
3
4
5
6

7
A B
Product Count
Bananas 38
Oranges 25
Apples 41
Pears 40
Formula Description (Result)
=OFFSET(A1,MATCH("Pears",A2:A5, 0),1) Looks up Pears in column A and returns the value for Pears in column B ( 40).

The formula uses the following arguments.


Formula to look up a value in an unsorted list (OFFSET function)

Callout 1 A1: The upper left cell of the range, also called the starting cell.
Callout 2 MATCH("Pears",A2:A5, 0): The MATCH function determines the row number below the starting cell to find the look up value.
Callout 3 "Pears": The value to find in the lookup column.
Callout 4 A2:A5: The column for the MATCH function to search. Don't include the starting cell in this range.
Callout 5 1: The number of columns to the right of the starting cell to find the lookup value.

For more information about how to use these functions, see MATCH function and OFFSET function.

Top of Page Top of Page

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

To do this task, use the HLOOKUP function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.
  4. Important For the example to work properly, you must paste it into cell A1 of the worksheet.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
  6. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

 
1
2
3
4
5
6

7
A B Bolts
Status Axles 9
In stock 4 10
On order 5 11
Back order 6
Formula Description (Result)
=HLOOKUP("Bolts", A1:C4, 3, FALSE) Looks up Bolts in row 1, and returns the value from row 3 that's in the same column (10)

For more information about how to use this function, see HLOOKUP function.

Top of Page Top of Page

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

To do this task, use the HLOOKUP function.

 Important    This method only works if the values in the first row have been sorted in ascending order.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

How do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.

Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.
  4. Important For the example to work properly, you must paste it into cell A1 of the worksheet.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
  6. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
 
1
2
3






4
A B C D
10000 50000 100000 Sales Volume
.05 .20 .30 Rate
Formula Description (Result)
=HLOOKUP(78658,A1:D4,2, TRUE) Looks up $78,658 in Row 1, finds the next largest value that is less than $78,658, which is $50,000, and then returns the value from row 2 that's in the same column as $50,000 (20%)

 Notes 

  • You can display the rate and return number as a percentage. Select the cell, and then on the Home tab, in the Number group, click Percent Style Button image.
  • You can display the Sales Volume number as dollars. Select the cell, and then on the Home tab, in the Number group, click Accounting Number Format Button image.

For more information about how to use this function, see HLOOKUP function.

Top of Page Top of Page

Create a lookup formula with the Lookup Wizard

The Lookup Wizard creates the lookup formula based on a worksheet data that has row and column labels. The Lookup Wizard helps you find other values in a row when you know the value in one column, and vice versa. The Lookup Wizard uses INDEX and MATCH in the formulas that it creates.

  1. Click a cell in the range.
  2. On the Formulas tab, in the Solutions group, click Lookup.
  3. If the Lookup command is not available, then you need to load the Lookup Wizard add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) program.

How to load the Lookup Wizard Add-in program

  1. Click the Microsoft Office Button Button image, click Excel Options, and then click the Add-ins category.
  2. In the Manage box, click Excel Add-ins, and then click Go.
  3. In the Add-Ins available dialog box, select the check box next to Lookup Wizard, and then click OK.
  4. Follow the instructions in the wizard.

Top of Page Top of Page

 
 
Applies to:
Excel 2007