BenchmarkPrep › Guides
VLOOKUP practice: exercises with answers
Almost every Excel test for a job has a lookup task. Work through these exercises in order: each one shows the formula and the mistake it's designed to catch.
Updated 6 November 2026
How VLOOKUP works
=VLOOKUP(lookup_value, table, column_number, FALSE) finds lookup_value in the first column of table and returns the value from column_number in the same row. The last argument, FALSE, means "exact match". Use it unless the task is about bands or tiers.
All the exercises use this product list in A1:C5:
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P01 | Widget | $12.50 |
| 3 | P02 | Gadget | $45.00 |
| 4 | P03 | Gizmo | $8.00 |
| 5 | P04 | Doohickey | $22.00 |
Exercise 1: a single lookup
Task: return the price of product P03.
Answer: =VLOOKUP("P03",A2:C5,3,FALSE) returns $8.00. Price is the 3rd column of the table, so the column number is 3.
Exercise 2: look up a code typed in a cell, then copy down
Task: an order list has product codes in F2:F6. In G2, return each order's product name, then copy it down to G6.
Answer: =VLOOKUP(F2,$A$2:$C$5,2,FALSE), copied down. The $ signs lock the table so it doesn't slide down as you copy; without them, the last rows return #N/A. This is the single most common lookup mistake in tests.
Exercise 3: handle a missing code
Task: the order list includes code P09, which isn't in the product list. Show "Not found" instead of #N/A.
Answer: =IFERROR(VLOOKUP(F2,$A$2:$C$5,2,FALSE),"Not found"). IFERROR replaces any error with the value you give it.
Exercise 4: approximate match for bands
Task: discounts depend on spend: under $100 gets 0%, $100 or more gets 5%, $500 or more gets 10%, $1,000 or more gets 15%. The bands are in I2:J5 (lowest first). Return the discount for a spend of $650.
| A | B | |
|---|---|---|
| 1 | Spend from | Discount |
| 2 | $0.00 | 0% |
| 3 | $100.00 | 5% |
| 4 | $500.00 | 10% |
| 5 | $1,000.00 | 15% |
Answer: =VLOOKUP(650,I2:J5,2,TRUE) returns 10%. With TRUE, VLOOKUP finds the largest value that's less than or equal to 650. The first column must be sorted smallest to largest for this to work.
Exercise 5: look to the left
Task: return the code for the product named Gizmo.
Answer: VLOOKUP can't do this, because it only returns values to the right of the column it searches, and Code is to the left of Product. Use =XLOOKUP("Gizmo",B2:B5,A2:A5) or =INDEX(A2:A5,MATCH("Gizmo",B2:B5,0)), which both return P03.
The mistakes that cost marks
- Leaving out FALSE. Without it VLOOKUP does an approximate match, which can quietly return the wrong row.
- Not locking the table with $ before copying down.
- Counting columns from column A instead of from the first column of the table.
- Extra spaces in the data ("P01 " isn't "P01"). TRIM fixes them.
Frequently asked questions
Is VLOOKUP still used in Excel tests?
Yes. It's in almost every intermediate Excel test. Newer tests often accept XLOOKUP or INDEX + MATCH as well, because they check the result, not which function you used.
Should I learn XLOOKUP instead of VLOOKUP?
Learn both if you can. VLOOKUP works in every version of Excel and is what most tests expect; XLOOKUP is simpler and can look left. INDEX + MATCH works everywhere and can also look left.
Why does my VLOOKUP return #N/A?
The value wasn't found in the first column of the table. Common causes are a missing $ on the table range after copying, extra spaces, numbers stored as text, or a code that genuinely isn't in the list.
Where can I practise VLOOKUP in a real spreadsheet?
Our free Excel test includes a lookup task in a live spreadsheet, marked instantly with a model answer.