VLOOKUP in Excel: explained with one example

VLOOKUP looks for a value in the first column of a table and returns something from the same row. The V means vertical — it searches down a column.

The four parts

=VLOOKUP(what_to_find, table, column_number, FALSE)

• what_to_find — the value, or the cell that holds it.
• table — the range. The first column must contain what you are looking for.
• column_number — which column of the table to return. 1 is the first, 2 the second.
• FALSE — exact match. Always write it.

Example

=VLOOKUP("Pen", A1:B3, 2, FALSE)

Result: 2.50. Excel finds “Pen” in column A and returns column 2 of the table.

Two mistakes that cause #N/A

The value is not in the first column. VLOOKUP only searches the leftmost column of the table you gave it. Move the table or switch to XLOOKUP.

Extra spaces or text-numbers. “Pen ” with a space is not “Pen”. A number stored as text does not match a real number. Use TRIM or reformat the cells.

Copying the formula down

Lock the table with dollar signs so it does not slide: =VLOOKUP(D1, $A$1:$B$3, 2, FALSE). Press F4 after selecting the range to add them.

Newer Excel? XLOOKUP vs VLOOKUP shows why most people switch.