XLOOKUP explained, with practice exercises
XLOOKUP looks for a value in one range and returns the matching value from another range. No column number, exact match by default, in any direction.
In short: =XLOOKUP(lookup_value,lookup_array,return_array). Excel finds the value in the lookup array and returns the value at the same position in the return array.
Try it now
Find the price for the item number in E2. Click the yellow cell and write the formula. While typing, click a cell to insert its reference.
Task In F2, use XLOOKUP to find the price of the item in E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item no. | Item | Price $ | Lookup | Price | |
| 2 | A-101 | Ballpoint pen | 4.9 | A-103 | ||
| 3 | A-104 | Stapler | 19.9 | |||
| 4 | A-103 | Notepad | 7.2 | |||
| 5 | A-102 | Binder | 12.5 | |||
| 6 | A-105 | Eraser | 3.4 |
The XLOOKUP formula
| Argument | Meaning | In the example |
|---|---|---|
| lookup_value | The value you are looking for | E2 |
| lookup_array | The column or row to search | A2:A6 |
| return_array | The column or row the result comes from | C2:C6 |
| if_not_found (optional) | What to show if nothing is found | "not found" |
| match_mode (optional) | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match | omitted |
| search_mode (optional) | 1 first to last (default), -1 last to first | omitted |
Show your own text instead of #N/A
If the value does not exist, XLOOKUP returns #N/A. The fourth argument lets you decide what appears instead, which replaces the old IFERROR around VLOOKUP.
Task The number in E2 does not exist. Make F2 show “not found” instead of #N/A.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item no. | Item | Price $ | Lookup | Price | |
| 2 | A-101 | Ballpoint pen | 4.9 | A-109 | ||
| 3 | A-104 | Stapler | 19.9 | |||
| 4 | A-103 | Notepad | 7.2 | |||
| 5 | A-102 | Binder | 12.5 | |||
| 6 | A-105 | Eraser | 3.4 |
XLOOKUP vs VLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Exact match | only with FALSE at the end | default |
| Column number needed | yes | no |
| Look to the left | not possible | possible |
| Text if not found | only with IFERROR | built in |
| Inserting a column | column number can break | ranges adjust |
| Available in | all versions | Microsoft 365, Excel 2021 and newer |
If you know VLOOKUP, XLOOKUP takes minutes to learn. Instead of one big table and a column number, you give two ranges: one to search and one for the result.
XLOOKUP with multiple criteria
Join the lookup values and the lookup arrays with &, for example =XLOOKUP(E2&F2,A2:A6&B2:B6,C2:C6). Excel then compares both criteria at the same time. Both methods, with practice, are in XLOOKUP with multiple criteria.
Which Excel versions have XLOOKUP?
XLOOKUP is available in Microsoft 365, Excel 2021, Excel 2024 and the current apps for Mac, iPhone, iPad and Android. Excel 2019 and Excel 2016 do not have XLOOKUP. Opening a file with XLOOKUP there shows #NAME?. In older versions, use VLOOKUP or INDEX with MATCH: =INDEX(C2:C6,MATCH(E2,A2:A6,0)).
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
In most cases, yes: exact match by default, no column number and it can look to the left. VLOOKUP is still needed for older Excel versions and existing files.
Does XLOOKUP work in Google Sheets?
Yes, Google Sheets has an XLOOKUP function with the same basic arguments.
Why does XLOOKUP show #NAME?
Usually your Excel version is older than 2021 and does not know the function. Less often the function name is misspelled.