How to Use VLOOKUP in Excel (Step-by-Step Examples)

VLOOKUP is the formula that pulls information from one table into another — match a product code, get its price; match an employee, get their department. It's one of the most useful functions in Excel and one of the most commonly misused. Here's how to get it right.

The syntax. VLOOKUP takes four arguments:

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

lookup_value — what you're searching for (e.g. a product code in A2).
table_array — the range to search; the lookup value must be in its first column.
col_index_num — which column number in that range holds the answer (counting from the left of the range, starting at 1).
range_lookup — FALSE for an exact match, TRUE for an approximate match.

A worked example. Say you have a product list on a sheet named "Products" with codes in column A, names in B, and prices in C. On your orders sheet you have a code in A2 and want its price:

=VLOOKUP(A2, Products!A:C, 3, FALSE)

This searches column A of Products for the code in A2 and returns the value from the third column (price). The FALSE ensures you only get a result for an exact code match.

Always use FALSE. The single most common VLOOKUP bug is leaving off the last argument or setting it to TRUE. Approximate match assumes your data is sorted and will return the closest value below — silently giving wrong answers. Unless you're deliberately looking up a value in a sorted range (like tax brackets), use FALSE.

Common errors.

#N/A — the value wasn't found. Check for extra spaces, text-vs-number mismatches (a code stored as text won't match the same code stored as a number), or that the value really exists in the first column.
Wrong value returned — your col_index_num is counting from the wrong place. It counts columns within the table_array, not the worksheet.
#REF! — your col_index_num is larger than the number of columns in the range.

The key limitation. VLOOKUP can only look to the right of the lookup column. If the value you want sits to the left of your matching column, VLOOKUP can't reach it.

Better alternatives. If your Excel version has it, XLOOKUP replaces VLOOKUP with cleaner syntax, can search in any direction, and has built-in "if not found" handling:

=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

On older versions, INDEX/MATCH does the same job and also looks both directions. See our comparison for which to choose.