XLOOKUP()
The modern replacement for VLOOKUP — searches any direction, defaults to an exact match, and returns a custom value when nothing is found.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])XLOOKUP searches lookup_array for lookup_value and returns the corresponding item from return_array — a separate range, not a column count, so it doesn't break when columns are inserted or deleted.
Unlike VLOOKUP, it can look to the left of the lookup column, defaults to an exact match, and lets you supply an if_not_found value instead of an #N/A error.
Available in Excel 365 and Excel 2021+. Older versions need VLOOKUP or INDEX/MATCH instead.
Arguments
- lookup_value
- The value to search for.
- lookup_array
- The range to search.
- return_array
- The range to return a value from — same size as lookup_array.
- if_not_foundoptional
- What to return instead of #N/A when no match is found.
- match_modeoptional
- 0 for exact match (default), -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard.
Examples
=XLOOKUP(D2, A:A, B:B)→$14.99
Exact match by default — no FALSE argument needed.
=XLOOKUP(D2, A:A, B:B, "Not found")→Not found
Returns a custom message instead of #N/A.