Home › Excel functions › XLOOKUP
XLOOKUP Formula in Excel: How to Use It, with Examples
The XLOOKUP formula searches a range for a value and returns the matching item from another range. It replaces VLOOKUP and HLOOKUP: it can look left or right, finds exact matches by default, and lets you choose what to show when nothing is found.
Excel CSE · 4 min readIn this guide
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Only the first three arguments are required.
| Argument | What it means |
|---|---|
| lookup_value | The value to find, such as "Mango" or a cell like F2. |
| lookup_array | The single column (or row) to search in. |
| return_array | The column (or row) to return a value from. It must be the same size as lookup_array. |
| if_not_found (optional) | What to show when there's no match, such as "Not found". Without it you get #N/A. |
| 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 from first to last (default), -1 from last to first. 2 and -2 are fast binary searches on sorted data. |
Which Excel versions have XLOOKUP? Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. In Excel 2019 and older the formula returns #NAME?. Use VLOOKUP or INDEX/MATCH there.
Basic example: look up a price
We'll use this product list for all examples on this page.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Product | Stock | Price |
| 2 | P-101 | Apple | 120 | $0.35 |
| 3 | P-102 | Banana | 85 | $0.25 |
| 4 | P-103 | Cherry | 300 | $0.10 |
| 5 | P-104 | Mango | 40 | $1.20 |
| 6 | P-105 | Pear | 60 | $0.30 |
This returns $1.20. Excel searches B2:B6 for "Mango", finds it in the 4th position, and returns the 4th value from D2:D6. There's no column number to count, so inserting a column later won't break the formula.
In practice you'll point to a cell instead of typing the value: =XLOOKUP(F2, $B$2:$B$6, $D$2:$D$6).
Look up a value to the left
VLOOKUP can only return columns to the right of the lookup column. XLOOKUP doesn't care about direction. To find the SKU of the mango, which sits to the left of the product name:
=XLOOKUP("Mango", B2:B6, A2:A6)This returns P-104.
Show a custom message when nothing is found
Use the fourth argument instead of wrapping the formula in IFERROR:
=XLOOKUP("Kiwi", B2:B6, D2:D6, "Not found")Kiwi isn't in the list, so the result is Not found instead of #N/A. You can also return 0 or an empty string "".
Return several columns at once
If the return array is more than one column wide, XLOOKUP returns the whole matching row of that range. The result "spills" into the cells to the right.
=XLOOKUP("Mango", B2:B6, C2:D6)Entered in F2, this puts the stock (40) in F2 and the price ($1.20) in G2. Make sure the cells it spills into are empty, otherwise you'll see #SPILL!.
Approximate match: tiers and brackets
For bands like grades or discount tiers, set match_mode to -1 (exact match or the next smaller value).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Min. score | Grade | Score | Grade | |
| 2 | 0 | F | 73 | B | |
| 3 | 50 | D | |||
| 4 | 60 | C | |||
| 5 | 70 | B | |||
| 6 | 80 | A |
There's no 73 in column A, so XLOOKUP takes the next smaller value, 70, and returns B. Note the empty fourth argument: the two commas skip if_not_found. Unlike VLOOKUP's approximate match, this works even if the list isn't sorted.
Find the last match instead of the first
Set search_mode to -1 to search from the bottom up. That's handy for logs and order lists where you want the most recent entry: =XLOOKUP("Anna", A2:A100, C2:C100, , 0, -1) returns the last value for Anna.
Common XLOOKUP errors
#N/A
No match was found. Check for typos, extra spaces (TRIM removes them) and numbers stored as text. Or add an if_not_found value.
#VALUE!
The lookup array and return array have different sizes, for example B2:B6 and D2:D7. Make them the same height.
#NAME?
Your version of Excel doesn't have XLOOKUP, or the function name is misspelled.
#SPILL!
The formula returns more than one value, but cells it needs are not empty. Clear the cells to the right or below.
XLOOKUP vs. VLOOKUP
| XLOOKUP | VLOOKUP | |
|---|---|---|
| Default match | Exact | Approximate (unless you add FALSE) |
| Look to the left | Yes | No |
| Breaks when columns are inserted | No | Yes (fixed column number) |
| Custom "not found" value | Built in | Needs IFERROR |
| Search from the bottom | Yes | No |
| Works in Excel 2019 and older | No | Yes |
If everyone who opens the file has a recent version of Excel, use XLOOKUP. If the file goes to people on older versions, stick with VLOOKUP or INDEX/MATCH.
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
In most cases, yes. It's exact by default, can look in any direction, doesn't break when columns move and has a built-in "not found" option. The only downside is that it doesn't exist in Excel 2019 and older.
Is XLOOKUP case-sensitive?
No. "mango" and "MANGO" match the same row.
Can XLOOKUP return more than one value?
It can return a whole row of values when the return array is several columns wide. To return every row that matches, use the FILTER function instead.
Why does XLOOKUP show #NAME?
Your Excel version doesn't support XLOOKUP. It's available in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web.