Lookups: VLOOKUP, XLOOKUP and INDEX/MATCH

Find a value in a table by a key.

A lookup finds a row by a key (an ID or name) and returns a value from another column.

=VLOOKUP(A2, Prices!A:C, 3, FALSE)       # key, table, column number, exact match
=XLOOKUP(A2, Prices!A:A, Prices!C:C, "not found")
=INDEX(Prices!C:C, MATCH(A2, Prices!A:A, 0))

• VLOOKUP looks only to the right of the key column and breaks if you insert columns. Always use FALSE (or 0) for an exact match.
• XLOOKUP (Excel 365 and Google Sheets) looks in any direction, has a built-in "not found" value and does not break when columns move.
• INDEX with MATCH works in every version and in any direction.

CarefulWith the last argument omitted or TRUE, VLOOKUP does an approximate match that needs sorted data and can return a wrong row without any error.

Common causes of #N/A

• The key really is not in the table.
• Extra spaces: clean the key with TRIM(A2).
• Number stored as text on one side only.