VLOOKUP practice exercises
VLOOKUP finds a value in the first column of a table and returns something from the same row. It is the formula most people learn first for joining two lists, and the one that most often returns #N/A for reasons that are easy to fix once you have seen them.
Syntax
VLOOKUP(search_key, range, index, [is_sorted])
search_key- The value to find, such as a SKU or a name.
range- The table. VLOOKUP only searches its first column.
index- Which column of the range to return, counting from 1 at the left edge of the range.
is_sorted- Use
FALSEfor an exact match. Leaving it out means TRUE (approximate match), which is the classic source of wrong answers.
The practice data
A product list with SKU, name, price and stock. F2 holds a SKU to look up. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Stock | SKU to find | |
| 2 | 101 | Widget | 5 | 40 | 103 | |
| 3 | 102 | Gadget | 20 | 15 | ||
| 4 | 103 | Gizmo | 50 | 0 | ||
| 5 | 104 | Doohickey | 12 | 8 | ||
| 6 | 105 | Sprocket | 3 | 120 |
Worked example
=VLOOKUP(102,A2:D6,3,FALSE)
Finds SKU 102 in column A, then returns the 3rd column of the range (Price). Result:
| 20 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return the product name for the SKU typed in F2.
2 Return the price of SKU 105.
3 Return the stock value of SKU 104: its stock multiplied by its price.
Master VLOOKUP in the full course
Lesson 9 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon VLOOKUP mistakes
- Leaving out the last argument. Without
FALSE, VLOOKUP assumes the first column is sorted and can quietly return the wrong row. - Counting the column index from column A of the sheet instead of from the left edge of the range.
- Trying to return a column to the left of the search column. VLOOKUP can't; use XLOOKUP or INDEX/MATCH.
- Not locking the range with
$before filling the formula down, so the table range slides off the data. - Numbers stored as text (or text that looks like a number) don't match, which gives
#N/Aeven though the value looks identical.
FAQ
Why does my VLOOKUP return #N/A?
The search key isn't in the first column of the range. Check for extra spaces, numbers stored as text, or a range that starts at the wrong column. Wrap it in IFERROR to show a friendly message instead.
Should I use VLOOKUP or XLOOKUP?
XLOOKUP is easier and more flexible: it can look left, defaults to an exact match and has a built-in "not found" value. VLOOKUP is still everywhere in existing sheets, so it's worth knowing both.