What is the XLOOKUP Function
The XLOOKUP function is the modern replacement for VLOOKUP and HLOOKUP. It searches any column and returns a value from any other column, works left or right, and has built-in error handling. XLOOKUP is available in Excel 365 and 2021 and later.
Formula: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Practical Example 1
Table A2:A3 has IDs 101 and 105, C2:C3 has salaries 30000 and 45000. Look up salary for ID 105.
Type: =XLOOKUP(105, A2:A3, C2:C3, Not Found)
Result: 45000
Practical Example 2
Look up to the left of the key column.
Type: =XLOOKUP(105, C2:C3, A2:A3)
Result: the ID for the salary
Practical Example 3
Two-way lookup.
Type: =XLOOKUP(id, A:A, XLOOKUP(col, B1:D1, B:D))
Result: the value at the intersection
Practical Example 4
Use if_not_found for clean error handling.
Type: =XLOOKUP(999, A2:A3, C2:C3, Not Found)
Result: Not Found (no error shown)
When to Use It
XLOOKUP is used for employee records, for product lookups, for two-way lookups, for replacing old VLOOKUP formulas, and for any lookup where you want more flexibility and built-in error handling.