XLOOKUP Function in Excel
XLOOKUP is a modern lookup function introduced in Microsoft Excel that replaces VLOOKUP and HLOOKUP. It can search vertically, horizontally, left-to-right, right-to-left, and return exact matches by default.
Important: XLOOKUP is more flexible and powerful than VLOOKUP because it can look in any direction and has built-in error handling.
XLOOKUP Syntax
=XLOOKUP(
lookup_value,
lookup_array,
return_array,
[if_not_found],
[match_mode],
[search_mode]
)
Required Arguments
These first three arguments are mandatory.
| Argument | Description |
|---|---|
| lookup_value | Value to search for. |
| lookup_array | Column or row where Excel searches. |
| return_array | Column or row from which result is returned. |
Sample Student Table
| ID | Name | Course | Fees |
|---|---|---|---|
| 101 | Rahul | Excel | 5000 |
| 102 | Priya | Python | 7000 |
| 103 | Amit | Tally | 6000 |
| 104 | Sneha | Power BI | 8000 |
Example 1 – Find Student Name
=XLOOKUP(102,A2:A5,B2:B5)
| Argument | Value Used | Meaning |
|---|---|---|
| lookup_value | 102 | Search Student ID 102 |
| lookup_array | A2:A5 | Search in ID column |
| return_array | B2:B5 | Return Name column |
Result: Priya
Example 2 – Find Course
=XLOOKUP(103,A2:A5,C2:C5)
Result: Tally
Example 3 – Find Fees
=XLOOKUP(104,A2:A5,D2:D5)
Result: 8000
Optional Arguments
XLOOKUP provides three optional arguments for advanced searching.
| Argument | Purpose |
|---|---|
| if_not_found | Custom message when value is not found. |
| match_mode | Controls exact or approximate matching. |
| search_mode | Controls search direction. |
Optional Argument 1: if_not_found
Displays a custom message if no matching value exists.
=XLOOKUP(110,A2:A5,B2:B5,"Student Not Found")
Result: Student Not Found
Optional Argument 2: match_mode
| Value | Meaning |
|---|---|
| 0 | Exact Match (Default) |
| -1 | Exact or Next Smaller Item |
| 1 | Exact or Next Larger Item |
Example
=XLOOKUP(102,A2:A5,B2:B5,"Not Found",0)
Result: Priya
Optional Argument 3: search_mode
| Value | Meaning |
|---|---|
| 1 | Search First to Last (Default) |
| -1 | Search Last to First |
Example
=XLOOKUP(104,A2:A5,B2:B5,"Not Found",0,-1)
Excel searches from the bottom of the list toward the top.
XLOOKUP Searching Left
Unlike VLOOKUP, XLOOKUP can return values from the left side of the lookup column.
=XLOOKUP("Python",C2:C5,B2:B5)
Result: Priya
XLOOKUP vs VLOOKUP
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Search Left | No | Yes |
| Built-in Error Handling | No | Yes |
| Column Number Required | Yes | No |
| Vertical Lookup | Yes | Yes |
| Horizontal Lookup | No | Yes |
