VLOOKUP

VLOOKUP ("vertical lookup") finds a value in the first column of a table and returns a value from another column in the same row. It's how you fetch a price from a product list, a name from an ID, a grade from a score band.

=VLOOKUP(lookup_value, table, column_number, FALSE)

An example

A product list in A1:C5:

To find the price of P03:

=VLOOKUP("P03", A2:C5, 3, FALSE)     → 279.00
=VLOOKUP("P02", A2:C5, 2, FALSE)     → Mouse

• "P03": what to look for, which must be in the table's first column,
• A2:C5: the table,
• 3: return the value from the table's 3rd column (Price),
• FALSE: exact match only.

Always use FALSE (for most lookups)

With the last argument TRUE or left out, VLOOKUP does an approximate match: it assumes the first column is sorted and returns the nearest smaller value. On unsorted data, that silently returns wrong answers. Use FALSE unless you're deliberately matching bands, like tax brackets or grade boundaries.

Lock the table with $

When you fill a VLOOKUP down, the table range must not move. Make it absolute with dollar signs:

=VLOOKUP(E2, $A$2:$C$5, 3, FALSE)

Now E2 changes as you fill down, but $A$2:$C$5 stays put. (Press F4 while editing a reference to add the dollar signs.)

Common VLOOKUP problems

For lookups to the left, or more flexibility, use XLOOKUP (Microsoft 365 and Excel 2021) or INDEX/MATCH, covered in the next lessons:

=XLOOKUP("P03", A2:A5, C2:C5, "Not found")