Excel track · Exercise 33 of 86
VLOOKUP: exact match
Pull a salary out of a table by its ID, with the exact-match flag written out.
Intermediate
Employee directory
Read-only data · answer in E2
In E2, look up the salary for the ID in D2 using the table in A1:C6.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Name | Salary | Lookup | Result |
| 2 | E-101 | Ana | 61500 | E-104 | |
| 3 | E-102 | Bo | 57000 | ||
| 4 | E-103 | Cy | 88000 | ||
| 5 | E-104 | Dee | 54000 | ||
| 6 | E-105 | Eli | 49500 |
Reference answer
What it does
VLOOKUP reads down the first column of a table until it finds the lookup value, then returns the cell in that row at the column number you name. The ID in D2 is E-104, which sits in row 5. Column 3 of that row holds 54000, so the formula =VLOOKUP(D2,A2:C6,3,FALSE) returns 54000.
The fourth argument decides the mode. FALSE, or 0, asks for an exact hit and gives #N/A when the ID is absent. TRUE, or nothing at all, matches approximately: it stops on the last row it passed, so an ID column can return another employee's salary.
Write FALSE every time on an ID column. Left out, the match is the silent kind, and a wrong salary looks like a right one until payroll disagrees.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Worked example
On Employee directory, =VLOOKUP(D2,A2:C6,3,FALSE) returns 54000.
=VLOOKUP(D2,A2:C6,3,FALSE)Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | The ID is missing from the first column, often because it carries a trailing space or is stored as a number rather than text. |
#REF! | The column number is wider than the table: 4 applied to A:C, which holds three columns. |
#VALUE! | The second argument is not a range of cells, or the column number is zero. |
At work
Payroll runs, price lists and product feeds: two exports joined by a key, one of them read for a figure.