.jpeg)
XLOOKUP is the modern lookup function in Excel. It searches for a value in one range and returns the matching value from another range. It was created to fix the limits of VLOOKUP, so it can look in any direction, does not need a column number, and has a built-in option for values that are not found.
Availability: XLOOKUP works in Microsoft 365, Excel 2021 and later, and Excel for the web. It is not available in Excel 2019 or older versions. If you share a file with someone who uses an older version, the formula will show an error for them.
This article explains how to use XLOOKUP in Excel with examples, output, and explanation.
XLOOKUP Syntax
It has the following syntax.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Meaning |
|---|---|
| lookup_value | The value you want to find |
| lookup_array | The range in which Excel searches |
| return_array | The range from which Excel returns the result |
| if_not_found | Optional. The text to show when no match is found |
| match_mode | Optional. 0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match |
| search_mode | Optional. 1 search first to last (default), -1 search last to first |
Only the first three arguments are required. By default, XLOOKUP looks for an exact match.
Sample Data
The following employee table is stored in A1:D5.
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| 101 | Amit | IT | 30000 |
| 102 | Neha | HR | 35000 |
| 103 | Rahul | IT | 32000 |
| 104 | Priya | Sales | 38000 |
Example 1: Basic Lookup
To find the name of employee 103:
=XLOOKUP(103,A2:A5,B2:B5)
Output:
Rahul
Explanation:
Excel searches for 103 in the range A2:A5 and finds it in the third position. It then returns the third value from B2:B5, which is Rahul. You select the search range and the return range separately, so there is no column number to count.
Example 2: Look to the Left
VLOOKUP cannot return a value from a column to the left of the lookup column. XLOOKUP can, because the two ranges are independent. To find the employee ID of Priya:
=XLOOKUP("Priya",B2:B5,A2:A5)
Output:
104
Explanation:
Excel searches for "Priya" in the Name column and returns the matching value from the Employee ID column, which is on the left. This was not possible with VLOOKUP without rearranging the table.
Example 3: Show a Message When the Value Is Not Found
If the lookup value does not exist, XLOOKUP returns #N/A. The fourth argument lets you give a custom message without using IFERROR().
=XLOOKUP(105,A2:A5,B2:B5,"Not found")
Output:
Not found
Explanation:
Employee 105 is not in the list, so Excel returns the text from the if_not_found argument. This only handles the "not found" case, so other errors such as a wrong range are still shown, which makes mistakes easier to notice.
Example 4: Return Multiple Columns at Once
If the return range has several columns, XLOOKUP returns the whole row. To get the name, department, and salary of employee 103:
=XLOOKUP(103,A2:A5,B2:D5)
Output:
| Name | Department | Salary |
|---|---|---|
| Rahul | IT | 32000 |
Explanation:
The formula is typed in one cell, and the result spills into the next two cells on the right. Keep those cells empty, otherwise Excel shows a #SPILL! error.
Example 5: Approximate Match for Grades
XLOOKUP can also find values that fall in a range. Unlike VLOOKUP, the table does not need to be sorted when you use -1.
| Minimum Marks | Grade |
|---|---|
| 0 | F |
| 40 | C |
| 60 | B |
| 80 | A |
This table is in A2:B5. To find the grade for 72 marks:
=XLOOKUP(72,A2:A5,B2:B5,,-1)
Output:
B
Explanation:
The match mode -1 means "exact match, or the next smaller value". There is no 72 in the list, so Excel picks the next smaller value, which is 60, and returns B. The empty space between the two commas skips the if_not_found argument.
Example 6: Find the Last Match
By default, XLOOKUP returns the first match. If a value appears more than once, you can search from the bottom with the search mode -1.
| Customer | Amount |
|---|---|
| Amit | 500 |
| Neha | 700 |
| Amit | 900 |
This table is in A2:B4. To get the latest order amount of Amit:
=XLOOKUP("Amit",A2:A4,B2:B4,,0,-1)
Output:
900
Explanation:
Excel starts from the last row and moves upward, so it finds Amit's second entry first. A normal search would return 500, which is the first entry.
Example 7: Wildcard Search
With the match mode 2, you can use * for any number of characters and ? for a single character.
=XLOOKUP("Ra*",B2:B5,B2:B5,"Not found",2)
Output:
Rahul
Explanation:
The pattern "Ra*" matches any name that starts with Ra. Excel returns the first one it finds, which is Rahul.
XLOOKUP vs VLOOKUP
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Default match | Approximate (if the last argument is omitted) | Exact |
| Look to the left | Not possible | Possible |
| Column number | Required | Not required |
| Not found message | Needs IFERROR() | Built-in argument |
| Return multiple columns | One column per formula | Can return many columns |
| Search from the bottom | Not available | Available with search mode -1 |
| Excel versions | All versions | Microsoft 365, Excel 2021 and later |
You can read more about the difference between these functions and INDEX-MATCH in Excel Interview Questions.
Important Points About XLOOKUP
- XLOOKUP needs a lookup range and a separate return range, so it can look in any direction.
- It uses an exact match by default.
- The fourth argument shows a custom message when no match is found.
- It can return a whole row or several columns, and the result spills into neighbouring cells.
- Use match mode -1 for grade or slab tables, and search mode -1 to find the last match.
- It is not available in Excel 2019 or older, so use VLOOKUP or INDEX-MATCH if the file must work there.