XLOOKUP in Google Sheets
XLOOKUP is the modern replacement for VLOOKUP and INDEX/MATCH. You give it what to find, where to find it and what to return: =XLOOKUP(F1, A2:A20, C2:C20).
It uses an exact match by default, can return a column to the left of the lookup column, and has a built-in fourth argument for missing values: =XLOOKUP(F1, A2:A20, C2:C20, "Not found"), so you don't need IFERROR.
Syntax
=XLOOKUP(search_key, lookup_range, result_range, [missing_value])
Example
=XLOOKUP(F1, A2:A20, C2:C20, "Not found")
Finds F1 in column A and returns column C.
Common mistakes
- Lookup and result ranges of different lengths.
- Passing a whole table as the result range when you want one column.
- Sharing the sheet with people on old Excel versions that don't have XLOOKUP.
← INDEX + MATCH · LEFT, RIGHT & LEN → · All lessons