IntermediateLookup & Reference
Look up a unit price with VLOOKUP
Use VLOOKUP with exact match to pull prices from a product table.
VLOOKUP
8 min25 points
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.
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.
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 →