Suppose you want to look up an employee's phone extension by using his badge number. Or, you want to find commission rate for a given sales amount. Excel gives you several ways to up data in lists quickly and efficiently.
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.


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.


The formula uses the following arguments.
1. A2:B5: The entire range in which you are looking up values.
2. MATCH("Pears",A2:A5,0): The MATCH function determines the row number.
3. "Pears": The value to find in the lookup column.
4. A2:A5: The column for the MATCH function to search.
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.
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.
In this example, you know the frequency and want to look up the associated color.


For more information about how to use this function, see VLOOKUP function.
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 will be returned, and the first column isn't sorted alphabetically.
Example
The example may be easier to understand if you copy it to a blank worksheet.


The formula uses the following arguments.
1. A1: The upper left cell of the range, also called the starting cell.
2. MATCH("Pears",A2:A5, 0): The MATCH function determines the row number below the starting cell to find the look up value.
3. "Pears": The value to find in the lookup column.
4. A2:A5: The column for the MATCH function to search. Don't include the starting cell in this range.
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.
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.


For more information about how to use this function, see HLOOKUP function.
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.


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 .

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 .
For more information about how to use this function, see HLOOKUP function.
What happened to the Lookup Wizard?
The Lookup Wizard was replaced by the function wizard and the Lookup and reference functions (reference).
Formulas that were generated by this wizard will continue to work in this version of Excel.