Functions
XLOOKUP
Modern lookup function with fallback and match options
Try it
When to use it
When should I use XLOOKUP instead of VLOOKUP?
Use XLOOKUP whenever you can: it can search to the left, defaults to an exact match, and keeps working when columns are inserted or moved.
Can XLOOKUP return a default when nothing matches?
Yes. Pass a fourth argument as the if-not-found value, e.g. XLOOKUP(key, lookup, results, Not found).
Use cases
Return a default when the key is missing
Find a city's region code
Used in
operations · sales · retail
Syntax
XLOOKUP(search_key, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Examples
=XLOOKUP("b",{"a","b","c"},{10,20,30}) // => 20