The XLOOKUP function in Excel
XLOOKUP looks for a value in one range and returns the value in the same position from another range. Unlike VLOOKUP, the two ranges can be anywhere: the result can come from a column to the left, and you don't count column numbers.
It's available in Excel 365, Excel 2021 and newer, and Excel for the web. It isn't available in Excel 2019 or older, so files for older versions still need VLOOKUP or INDEX/MATCH.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | What it means |
|---|---|
| lookup_value | What to look for. |
| lookup_array | The column (or row) to search in. |
| return_array | The column (or row) to return a value from. Same size as lookup_array. |
| if_not_foundoptional | What to show if nothing matches, instead of #N/A. |
| match_modeoptional | 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match. |
| search_modeoptional | 1 = search from the first item (default), -1 = search from the last item. |
Examples
1. Find a price by product ID
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Stock | Product ID | P-102 | |
| 2 | P-101 | Desk lamp | 24.5 | 40 | Price | 149 | |
| 3 | P-102 | Office chair | 149 | 12 | |||
| 4 | P-103 | Monitor stand | 39.9 | 25 | |||
| 5 | P-104 | Keyboard | 59 | 0 |
Search the ID column A2:A5, return from the price column C2:C5. Exact match is the default, so no FALSE is needed.
2. Look to the left
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Stock | ID of the Keyboard | |
| 2 | P-101 | Desk lamp | 24.5 | 40 | P-104 | |
| 3 | P-102 | Office chair | 149 | 12 | ||
| 4 | P-103 | Monitor stand | 39.9 | 25 | ||
| 5 | P-104 | Keyboard | 59 | 0 |
The search column (B) is to the right of the result column (A). VLOOKUP can't do this; XLOOKUP can.
3. Your own message when nothing is found
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Stock | Product P-999 | |
| 2 | P-101 | Desk lamp | 24.5 | 40 | No such product | |
| 3 | P-102 | Office chair | 149 | 12 | ||
| 4 | P-103 | Monitor stand | 39.9 | 25 | ||
| 5 | P-104 | Keyboard | 59 | 0 |
The 4th argument replaces #N/A, so you don't need IFERROR around the formula.
4. Approximate match: price bands
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Shipping | Parcel weight | 6.3 | |
| 2 | 0 | 4.99 | Shipping | 12 | |
| 3 | 2 | 7.5 | |||
| 4 | 5 | 12 | |||
| 5 | 10 | 20 |
match_mode -1 means "exact, or else the next smaller value". 6.3 isn't in the list, so it uses 5 → 12. Unlike VLOOKUP, this works even if the list isn't sorted.
Common mistakes
#NAME? error. The Excel version doesn't have XLOOKUP (Excel 2019 or older).
Fix: Use VLOOKUP or INDEX/MATCH for files that must open in older versions.
#VALUE! error. The lookup and return ranges have different sizes, e.g. A2:A5 and C2:C6.
Fix: Make both ranges start and end on the same rows.
Ranges shift when copying the formula down.
Fix: Lock them with $ signs:
=XLOOKUP(G1,$A$2:$A$5,$C$2:$C$5).
Frequently asked questions
What's the difference between XLOOKUP and VLOOKUP?
XLOOKUP uses separate search and result ranges (so it can look left), defaults to an exact match, and has a built-in 'not found' value. VLOOKUP works in every Excel version.
Can XLOOKUP return several columns at once?
Yes. If return_array is several columns wide, e.g. B2:D5, the result spills into neighboring cells in Excel 365.
How do I find the last match instead of the first?
Set search_mode to -1: =XLOOKUP(G1,A2:A5,C2:C5,"",0,-1).
Related functions
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- INDEXINDEX returns the value at a given row and column of a range.
- MATCHMATCH returns the position of a value in a list, for example that "Lena" is the 3rd name.
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.