Lookup
Finding a row by its key
Definition
An exact-match VLOOKUP finds a key in the first column and returns a value from the same row, using FALSE so that near matches are never accepted.
Syntax
=VLOOKUP(9027,A2:C4,3,FALSE)
Arguments
| Argument | Meaning |
|---|---|
9027 |
The key to find in the first column of the table. |
A2:C4 |
The table that is searched. The key column must come first. |
3 |
The column of the table to return. |
FALSE |
Requests an exact match, so an unknown key gives #N/A instead of a wrong row. |
Worked example
An office keeps badge numbers, names, and floors in A2:C4.
| A: Badge | B: Name | C: Floor |
|---|---|---|
| 8841 | Ola | 2 |
| 9027 | Pia | 5 |
| 9313 | Rui | 1 |
Formula: =VLOOKUP(9027,A2:C4,3,FALSE)
Result: 5. Badge 9027 belongs to Pia, and the third column of her row holds floor 5.
Practice
The lunch menu is Z-10 Soup 6, Z-20 Salad 9, and Z-30 Wrap 14. On the sheet, codes are in A2:A4 and prices are in B2:B4. In D2, write an exact-match formula that returns the price for Z-30.
- In D2, write an exact-match VLOOKUP that returns the price for Z-30 from A2:B4.
- D2 should show the price for Z-30.
Related functions
MATCH can supply the column number for VLOOKUP, for example by finding the heading Floor, so the formula survives added columns.
Common mistake
Leaving off FALSE: the default is approximate matching, which on an unsorted list quietly returns a nearby row instead of an error.