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 FALSE for 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.

ABCDEF
1SKUProductPriceStockSKU to find
2101Widget540103
3102Gadget2015
4103Gizmo500
5104Doohickey128
6105Sprocket3120

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.

H2

2 Return the price of SKU 105.

H2

3 Return the stock value of SKU 104: its stock multiplied by its price.

H2

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 pricing

Common VLOOKUP mistakes

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.

Keep practicing