The modern replacement for VLOOKUP and HLOOKUP — search any column and return any column, in any direction.
XLOOKUP is available in Google Sheets (rolled out 2022) and behaves the same as in Excel.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])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")Result: $1,250
Matches the key in E2 and returns the price; shows "Not found" if missing.
=XLOOKUP(E2, Names, IDs)Result: A102
Returns a value to the left of the key — impossible with VLOOKUP.
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.