Function guides ยท Lookup
XLOOKUP function
Finds a value in one range and returns the matching item from another range.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | What it is |
|---|---|
lookup_value | The value to find. |
lookup_array | The range to search. |
return_array | The range to return from. Same size as lookup_array. |
if_not_found | Optional. What to return instead of #N/A. |
match_mode | Optional. 0 exact (default), -1 or 1 next smaller or larger, 2 wildcard. |
search_mode | Optional. 1 first to last (default), -1 last to first. |
What XLOOKUP does
XLOOKUP finds a value in one range and returns the item in the same position from another range. It does what VLOOKUP does without the column counting and without the leftmost-column rule.
You give it three things: what to find, where to look, and where to return from. Three optional arguments cover a missing key, the matching mode, and the search direction.
Defaults are sensible. It matches exactly unless you say otherwise, and it returns #N/A when nothing is found.
XLOOKUP examples
1. Look up a price, any column
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Product | Price | ||
| 2 | A-10 | Stapler | 12.5 | 34 | |
| 3 | B-22 | Lamp | 34 | ||
| 4 | C-05 | Chair | 89 |
XLOOKUP takes the search column and the return column separately. Here it searches the SKU column and returns from the price column, with no column number to count. The match is exact by default.
2. Return something to the left
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | ID | ||
| 2 | Dana | 7001 | Luis | |
| 3 | Luis | 7002 | ||
| 4 | Mei | 7003 |
Because the search and return ranges are independent, the answer can sit to the left of the key. Here the lookup finds a name by its employee number, which VLOOKUP cannot do.
3. A friendly message when nothing matches
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Price | ||
| 2 | A-10 | 12.5 | No such SKU | |
| 3 | B-22 | 34 |
The fourth argument replaces #N/A when the key is missing. That removes the need to wrap the lookup in IFERROR, and it will not hide other errors by accident.
Common XLOOKUP mistakes
- Mismatched range sizes. lookup_array and return_array must be the same height. If one is longer, the result is #VALUE!.
- Using it in an older version of Excel. XLOOKUP is in Excel 2021 and Microsoft 365. In Excel 2019 and earlier, the formula returns #NAME?. Use INDEX and MATCH there.
- Choosing a whole table as the return array. A multi-column return array spills across several cells. That is a feature, but if the cell to the right is occupied you get #SPILL!.
- Approximate match without sorting. XLOOKUP does not need sorted data for -1 or 1, but the result is the next smaller or larger item, not the closest. Read the mode before using it.
XLOOKUP on Excel job tests
XLOOKUP tasks read like VLOOKUP tasks: pull a field from a table by a key. The differences you can use are the not-found argument, a return column anywhere in the table, and the search direction.
"Show Not found when the ID is missing" is the fourth argument. "Return the most recent entry for this customer" in data sorted by date is a search mode of -1, which starts from the bottom.
Check which Excel the test uses. In Excel 2019 and earlier the formula shows #NAME?. If the instructions are silent, a VLOOKUP or an INDEX and MATCH is the safe choice.
Related functions
XLOOKUP questions
- What is the difference between XLOOKUP and VLOOKUP?
- XLOOKUP searches any column, returns from any column, defaults to exact match, and has a not-found argument. VLOOKUP needs the key on the left and a column number.
- Is XLOOKUP available in Excel 2019?
- No. It is in Excel 2021 and Microsoft 365. In Excel 2019 use INDEX and MATCH.
- Can XLOOKUP return several columns?
- Yes, give it a multi-column return array and the result spills into neighboring cells.
- How do I find the last match instead of the first?
- Set the sixth argument, search_mode, to -1.
Practice XLOOKUP
Try it in a working grid: the free lesson VLOOKUP: find a value by its key (module 11 of Everyday Excel) has checked XLOOKUP exercises.
Practice this function