Lookup
VLOOKUP
Definition
VLOOKUP searches the first column of a table for a key and returns a value from another column in the same row.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The key to find, such as a code typed in quotes or held in a cell. |
table_array |
The block of cells to search. The key must be in its leftmost column. |
col_index_num |
Which column of the table to return, counting the leftmost one as 1. |
range_lookup |
Use FALSE for an exact match. TRUE or leaving it out allows approximate matching on sorted data. |
Worked example
A small shop stores product codes, names, and prices in A2:C4.
| A: Code | B: Product | C: Price |
|---|---|---|
| P-310 | Lamp | 36 |
| P-455 | Shelf | 58 |
| P-782 | Chair | 91 |
Formula: =VLOOKUP("P-455",A2:C4,3,FALSE)
Result: 58. The code P-455 is found in row 3 of the sheet, and column 3 of that row holds the price 58.
Practice
Range A2:C4 holds staff records: E-05, Mira, Lisbon; E-12, Tomas, Prague; and E-19, Ines, Madrid. Write an exact-match formula that returns the city for E-12.
- In D2, write an exact-match VLOOKUP that returns the city for E-12 from A2:C4.
- D2 should show the city for E-12.
Related functions
INDEX and MATCH can perform the same kind of lookup, and unlike VLOOKUP they can return a value from a column to the left of the key.
Common mistake
Searching for a key that is not in the leftmost column of table_array; VLOOKUP never looks left, so the result is #N/A.