The modern replacement for VLOOKUP and HLOOKUP — search any column and return any column, in any direction.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Arguments
lookup_valuerequired
The value to search for.
lookup_arrayrequired
The array or range to search.
return_arrayrequired
The array or range to return a value from.
if_not_foundoptional
Text to return when no match is found.
match_modeoptional
0 exact (default), -1/1 next smaller/larger, 2 wildcard.
XLOOKUP searches a lookup array and returns the matching item from a separate return array. It looks left or right, defaults to exact match, and has a built-in if_not_found argument so you no longer need IFERROR. Available in Microsoft 365 and Excel 2021+.
=XLOOKUP(E2, A2:A100, C2:C100, "Not found")→$1,250Matches the key in E2 and returns the price; shows "Not found" if missing.
=XLOOKUP(E2, Names, IDs)→A102Returns a value to the left of the key — impossible with VLOOKUP.
Name the three arrays
Set the lookup value, the column to search, and the column to return — they can be in any order.
Add a fallback
Use the if_not_found argument to replace #N/A with clear text.
Tune the match mode
Leave it blank for exact match, or use 2 for wildcard text searches.
For most cases yes: it searches in any direction, defaults to exact match, survives inserted columns, and handles not-found inline. Use VLOOKUP only when sharing with older Excel versions.
It requires Microsoft 365 or Excel 2021+. In older versions, use INDEX/MATCH for the same flexibility.