VLOOKUP practice exercises: seven worked examples
Seven VLOOKUP exercises from exact to approximate match, with the answers, the four causes of #N/A, and when to reach for XLOOKUP instead.
VLOOKUP looks a value up in the first column of a table. It returns something from the same row. It takes 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. Each exercise below works on one of them, and the answers say which.
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. Count the columns from the left edge of the table, not from column A of the sheet. Here they are the same. In the next table they will not be.
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. You do not touch the formula. That is the point of pointing at a cell. Use this form when the code is typed once, in one place, and the formulas read it.
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 only when a blank or a message is better to show. An IFERROR around a formula that is actually wrong hides the mistake. The checklist further down is for the #N/A you did not expect.
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 from low to high. 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, and 84.9 returns Merit. Tax bands, shipping brackets, commission tiers and discount thresholds all work this way.
Exercise 6 — let MATCH count the column
Back to the stock table. In F13, the number in stock for P200 again — but this time do not type the column number. Find it from the heading "In stock" in row 3.
Answer:
=VLOOKUP("P200",A4:D7,MATCH("In stock",A3:D3,0),FALSE) → 8
MATCH finds "In stock" in the heading row and says it is the 4th cell along. VLOOKUP uses that 4 as its column number. Now a column inserted between Product and Price does not break the formula. The number is worked out again each time. The 0 at the end of MATCH means exact, the way FALSE does in VLOOKUP.
Exercise 7 — part of a name
Somebody remembers the product starts with "Brake" and nothing else. In F15, find its price from the Product column.
Answer: =VLOOKUP("Brake*",B4:D7,2,FALSE) → 12
Two things changed. The table now starts at column B. VLOOKUP can only search the first column of the table it is given. The names are in B. So Price is now column 2, not 3. And the star is a wildcard. "Brake*" matches anything beginning with Brake. Wildcards only work with an exact match (FALSE), and only on text.
The #N/A checklist
When a lookup that should work returns #N/A, check these four things, in this order:
- A stray space. "P200 " with a space after it is not "P200". Wrap the lookup value, or the whole column, in TRIM.
- A number stored as text, or the other way round. Type 200 in a cell, then paste "200" from a website. They look the same and never match. A green triangle in the cell corner is the sign. VALUE and TEXT convert either way.
- The table does not start in the column being searched. VLOOKUP only looks in the first column of the range you gave it. Exercise 7 is the fix: start the table where the codes are.
- Approximate match on an unsorted table. A missing fourth argument means TRUE. On a table that is not sorted, TRUE returns the wrong row, or #N/A. Write FALSE unless you are doing exercise 5 on purpose.
When not to use VLOOKUP
VLOOKUP only looks to the right of the column it searches. It also counts columns by number. So a column inserted in the middle of the table breaks every formula pointing at it. There is no error. XLOOKUP fixes both. It searches any column, returns any column, and uses an exact match by default. If your Excel has it, learn it next. Older versions and some work computers do not have it. There, INDEX and MATCH together do the same job.
Questions people ask
Why does VLOOKUP return the wrong row?
Almost always the fourth argument. Left out, it means TRUE, an approximate match. On an unsorted table that returns the wrong thing, with no error to warn you. Type FALSE every time you want the exact code.
Can VLOOKUP return more than one column?
One formula returns one cell. To get Price and In stock together, write two formulas, with column numbers 3 and 4. Or move to XLOOKUP, which can return several columns at once.
Is VLOOKUP case-sensitive?
No. "p300" finds P300. If the case matters, you need EXACT inside an INDEX and MATCH. That is rare, and it is a later lesson.