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.
VLOOKUP examples
For more information, see VLOOKUP function.
INDEX and MATCH examples
In simple 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 looks for the first value in C2:C10 that corresponds to Kale (in B7) and returns the value in C7 (100), which is the first value that matches Kale.
For more information, see INDEX function and MATCH function.
Look up values vertically in a list by using an approximate match
To do this, use the VLOOKUP function.
Important: Make sure the values in the first row have been sorted in an ascending order.
In the above example, VLOOKUP looks for the first name of the student who has 6 tardies in the A2:B7 range. There is no entry for 6 tardies in the table, so VLOOKUP looks for the next highest match lower than 6, and finds the value 5, associated to the first name Dave, and thus returns Dave.
For more information, 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.
Note: 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.
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. You should not include the starting cell in the range.
1 is the number of columns to the right of the starting cell where the return value should be from. In our example, the return value is from column D, Sales.
Look up values horizontally in a list by using an exact match
To do this task, use the HLOOKUP function. See an example below:
HLOOKUP looks up the Sales column, and returns the value from row 5 in the specified range.
For more information, see HLOOKUP function.
Look up values horizontally in a list by using an approximate match
To do this task, use the HLOOKUP function.
Important: Make sure the values in the first row have been sorted in an ascending order.
In the above example, HLOOKUP looks for the value 11000 in row 3 in the specified 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.
