Let's say 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?
Use the VLOOKUP and HLOOKUP functions
Use the INDEX, MATCH, and OFFSET functions
Look up values vertically in a list by using an exact match
To do this task, use the VLOOKUP function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

A 
B 
C 
D 
Badge Number 
Last Name 
First Name 
Extension 
ID34567 
Davolio 
Nancy 
5467 
ID16782 
Fuller 
Andrew 
3457 
ID4537 
Leverling 
Janet 
3355 
ID1873 
Peacock 
Margaret 
5176 
ID3456 
Buchanan 
Steven 
3453 
ID5678 
Suyama 
Michael 
428 
Formula 
Description (Result) 
=VLOOKUP("ID4537", A1:D7, 4, FALSE) 
Lookup the badge number, ID4537, in the first column and return the matching value in the same row of the fourth column (3355) 

Function details
VLOOKUP
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.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
In this example, you know the frequency and want to look up the associated color.

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) 

Function details
VLOOKUP
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 to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

A 
B 
C 
Status 
Axles 
Bolts 
In stock 
4 
9 
On order 
5 
10 
Back order 
6 
11 
Formula 
Description (Result) 

=HLOOKUP("Bolts", A1:C4, 3) 
Looks up Bolts in row 1, and returns the value from row 3 that's in the same column (10) 


Function details
HLOOKUP
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.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

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
 To display the rate and return number as a percentage, select the cell and then click Percent Style on the Formatting toolbar.
 To display the Sales Volume number as dollars, select the cell and then click Dollar Style on the Formatting toolbar.
Function details
HLOOKUP
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.
 On the Tools menu, click Addins, select the Lookup Wizard box, and then click OK.
 Click a cell in the range.
 On the Tools menu, click Lookup.
 Follow the instructions in the wizard.
Top of Page
Look up values vertically in a list by using an exact match
To do this task, use the INDEX and MATCH functions.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

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 range (INDEX function)
A2:B5: The entire range in which you are looking up values.
MATCH("Pears",A2:A5,0): The MATCH function determines the row number.
"Pears": The value to find in the lookup column.
A2:A5: The column for the MATCH function to search.
2: The column from which to return the value. The leftmost column is 1.
Function details
INDEX
MATCH
Top of Page
Look up values vertically in a list of unknown size by using an exact
m
atch
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 to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

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.
A1: The upper left cell of the range, also called the starting cell.
MATCH("Pears",A2:A5, 0): The MATCH function determines the row number below the starting cell to find the look up value.
"Pears": The value to find in the lookup column.
A2:A5: The column for the MATCH function to search. Don't include the starting cell in this range.
1: The number of columns to the right of the starting cell to find the lookup value.
Function details
MATCH
OFFSET
Top of Page