VLOOKUP practice exercises: five worked examples
Five VLOOKUP exercises from exact match to approximate match, with the answers, the two mistakes that cause #N/A, and when to reach for XLOOKUP instead.
VLOOKUP looks a value up in the first column of a table and returns something from the same row. Four arguments, always in the same order: what to look for, where to look, which column to return, and whether an approximate match is allowed. Most VLOOKUP problems are one of those four being wrong, so each exercise below leans on one of them.
Put this stock table in cells A3 to D7 and do the exercises in column F.
| Row | A | B | C | D |
|---|---|---|---|---|
| 3 | Code | Product | Price | In stock |
| 4 | P100 | Inner tube | 4.5 | 26 |
| 5 | P200 | Brake pad | 12 | 8 |
| 6 | P300 | Chain | 24.99 | 5 |
| 7 | P400 | Saddle | 38.5 | 3 |
Exercise 1 — an exact match
In F3, find the price of product P300.
Answer: =VLOOKUP("P300",A4:D7,3,FALSE) → 24.99
Look for P300 in the first column of A4:D7, and return the third column of that table — Price. FALSE means the code has to match exactly. Text goes in quotation marks; a cell address does not.
Exercise 2 — the value comes from a cell
Type P200 into F5. In G5, find how many of that product are in stock.
Answer: =VLOOKUP(F5,A4:D7,4,FALSE) → 8
The same shape, but the thing to look for is whatever F5 holds. Change F5 to P400 and the answer becomes 3 without touching the formula — which is the point of pointing at a cell.
Exercise 3 — return text
In F7, find the name of product P400.
Answer: =VLOOKUP("P400",A4:D7,2,FALSE) → Saddle
Column 2 of the table is Product. VLOOKUP does not care whether the thing it returns is a number or a word.
Exercise 4 — when it cannot find anything
In F9, look up product P500. Then make the cell say Not stocked instead of an error.
Answer: =VLOOKUP("P500",A4:D7,3,FALSE) → #N/A,
and then =IFERROR(VLOOKUP("P500",A4:D7,3,FALSE),"Not stocked")
#N/A is not a broken formula: it is VLOOKUP saying, correctly, that there is no P500. Wrap it in IFERROR when a blank or a message is the better thing to show. The other three causes of #N/A — a stray space in the code, a number stored as text, and a table that does not start in the column being searched — are a lesson of their own.
Exercise 5 — an approximate match
Grades are awarded by band: put this table in cells A11 to B15. In F11, find the grade for a score of 72.
| Row | A | B |
|---|---|---|
| 11 | From | Grade |
| 12 | 0 | Fail |
| 13 | 50 | Pass |
| 14 | 70 | Merit |
| 15 | 85 | Distinction |
Answer: =VLOOKUP(72,A12:B15,2,TRUE) → Merit
With TRUE, VLOOKUP finds the largest value that is less than or equal to 72. That is 70, and 70's row says Merit. Two rules make this work: the first column must be sorted ascending, and the table must start at the lowest possible score — the 0 row is what stops a score of 30 returning #N/A. A score of 85 returns Distinction; 84.9 returns Merit.
When not to use VLOOKUP
VLOOKUP only looks to the right of the column it searches, and it counts columns by number, so inserting a column in the middle of the table silently breaks every formula pointing at it. XLOOKUP fixes both: it searches any column, returns any column, and defaults to an exact match. If your Excel has it, learn it next. If it does not — older versions and some corporate installs — INDEX and MATCH together do the same job and always have.