Lookups find a value in one table and return related information, such as a price for a product code or a manager for an employee ID.
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Example: product codes are in column A of a price list in A2:C100, and prices are in the third column:
=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)
FALSEasks for an exact match. Leave it out and Excel may return a wrong “approximate” match.- VLOOKUP only looks to the right of the first column.
- Use
$to lock the table range before copying the formula down.
XLOOKUP
Available in Microsoft 365 and Excel 2021 or later, XLOOKUP is simpler and more flexible:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
=XLOOKUP(E2, A2:A100, C2:C100, "Not found")
- It can look left or right.
- It does exact matches by default.
- It returns your own message instead of #N/A when nothing matches.
Fixing #N/A errors
- Extra spaces: clean the data with
TRIM(). - Numbers stored as text: convert them so the types match.
- The value really is missing: wrap the formula in
IFNA()or use XLOOKUP’s if_not_found argument.