How to use VLOOKUP in Excel (with examples)

VLOOKUP looks for a value in the first column of a range and returns a value from another column in the same row. It’s one of the most-used functions in Excel, and also one of the most misunderstood.

The syntax

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

• lookup_value — what you’re searching for
• table_array — the range to search (the lookup column must be first)
• col_index_num — which column of that range to return, counting from 1
• range_lookup — FALSE for an exact match (almost always what you want)

A worked example

Say prices live in A:B, with product names in column A and prices in column B. To find the price of the product typed in D2:

=VLOOKUP(D2, A:B, 2, FALSE)

The 2 means “return the value from the second column of the range.” FALSE forces an exact match.

Common errors

• #N/A — the value wasn’t found. Check for stray spaces or a mismatch between text and numbers.
• #REF! — col_index_num is larger than the number of columns in the range.
• Returning the wrong value — you left range_lookup blank or set it to TRUE, which does an approximate match on unsorted data.

When to use XLOOKUP instead

If you have a modern version of Excel, XLOOKUP is more flexible: it can look left, doesn’t break when you insert columns, and defaults to an exact match.

=XLOOKUP(D2, A:A, B:B)

Same result, fewer footguns.