Excel Practice Sheets

VLOOKUP

Finds a value in the first column of a table and returns a value from another column.

Syntax

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Lookup & Reference.

Practise VLOOKUP3 interactive exercises, free, no account needed

A worked example

=VLOOKUP(B2,F2:G6,2,FALSE)

Returns $18

Finds the item code from B2 in the first column of F2:G6 and returns the price.

Where it goes wrong

Omitting the final FALSE gives approximate match, which returns a wrong answer on unsorted data rather than an error — the worst kind of bug. The column number also counts from the left edge of your table range, not from column A.

Consider instead: XLOOKUP, which takes the return range directly and cannot break when a column is inserted.

What the exercises catch

Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.

  • Leaving off the last argument means approximate match, which assumes your table is sorted. It is not, so you can get a wrong price rather than an error — the worst kind of bug. Add FALSE.Look up a unit price with VLOOKUP
  • FALSE demands an exact match, and 32 is not in the rate card — so this returns #N/A. Banded lookups are the one case where you want the approximate mode.Look up a shipping band with VLOOKUP
  • Column 1 is the item code you searched for. The price is the second column of the table range, so the index is 2.Look up a unit price with VLOOKUP
  • This is right for the first parcel, but fill it down and the rate card slides with it — by the last row it is looking at a range that has run off the bottom of the table. Lock the range.Look up a shipping band with VLOOKUP

Practise VLOOKUP

Knowing what a function does and being able to write it from memory are different skills. These close the gap.

All VLOOKUP practice exercises →

Who practises VLOOKUP

Related functions