Lookup
Looking up a price by product key
Definition
Looking up a price by product key means giving VLOOKUP the key and a two-column table so it returns the matching price.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The product key to find, such as "K-2". |
table_array |
The table to search. The key must sit in the leftmost column. |
col_index_num |
Which column of the table to return. 2 is the price column in this lesson. |
range_lookup |
Use FALSE for an exact match. |
Worked example
A cafe keeps drink codes and prices in A2:B4.
| A: Code | B: Price |
|---|---|
| K-1 | 4 |
| K-2 | 9 |
| K-3 | 6 |
Formula: =VLOOKUP("K-2",A2:B4,2,FALSE)
Result: 9. K-2 is in the second row of the table, and column 2 of that row holds 9.
Practice
Codes P-1, P-2, and P-3 sit in A2:A4, and their prices 8, 15, and 6 sit in B2:B4. In C2, write a formula that returns the price for P-2.
- In C2, write a VLOOKUP formula that returns the price for P-2 from A2:B4.
- C2 should show the price for P-2.
Step 1 of 2
Enter =VLOOKUP("P-2",A2:B4,2,FALSE) in C2.
P-2 is the second row, and the second column is 15.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses VLOOKUP.
C2 now shows 15.
The next lesson is Looking up a city by code.
Related functions
VLOOKUP is the function that returns that price from the key.
Common mistake
Leaving off FALSE, as in =VLOOKUP("K-2",A2:B4,2): the default is approximate matching, which can return a nearby row instead of the exact price 9.