The XLOOKUP function is an advanced lookup function that replaces VLOOKUP and HLOOKUP. It allows you to search in any direction (left, right, up, or down) and returns exact matches by default.
It is more flexible, powerful, and easier to use.
Structure of XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Arguments:
- lookup_value → Value you want to find
- lookup_array → Range where Excel searches
- return_array → Range to return result from
- if_not_found → Optional custom message
- match_mode → Exact or approximate match
- search_mode → Search direction
Example 1: Basic XLOOKUP
If:
A2 = Employee ID
A5:A10 = Employee IDs
B5:B10 = Employee Names
Formula:
=XLOOKUP(A2, A5:A10, B5:B10)
This searches ID and returns the matching name.
Example 2: With Custom Message
=XLOOKUP(A2, A5:A10, B5:B10, "Not Found")
No need for IFERROR.
Example 3: Lookup to the Left
Unlike VLOOKUP, XLOOKUP can look left.
=XLOOKUP(A2, B5:B10, A5:A10)
This searches in column B and returns value from column A.
Match Modes
| Match Mode | Meaning |
|---|---|
| 0 | Exact match (default) |
| -1 | Exact or next smaller |
| 1 | Exact or next larger |
| 2 | Wildcard match |
Example:
=XLOOKUP(A2, A5:A10, B5:B10, , 1)
Search Modes
| Search Mode | Meaning |
|---|---|
| 1 | First to last (default) |
| -1 | Last to first |
| 2 | Binary search ascending |
| -2 | Binary search descending |
Advantages of XLOOKUP
- Searches in any direction
- Exact match by default
- No column index number required
- Handles errors inside formula
- Works vertically and horizontally
- Can return multiple columns
Example: Return Multiple Columns
=XLOOKUP(A2, A5:A10, B5:D10)
Returns multiple related values at once.
Why XLOOKUP is Important
It simplifies complex lookup tasks and is the modern replacement for older lookup functions.
XLOOKUP is recommended for new Excel users because it is powerful, flexible, and easy to use.