Excel practice › Excel functions › XLOOKUP
XLOOKUP in Excel: how it works, with examples you can edit
XLOOKUP looks for a value in one column (or row) and returns the value at the same position in another. To get the name of vendor V-331 when the IDs are in C2:C7 and the names in A2:A7: =XLOOKUP("V-331", C2:C7, A2:A7).
You point at two ranges, where to look and what to return, so the result can sit to the left or to the right of the column you search. Exact match is the default. XLOOKUP exists in Excel 2021, Excel 2024 and Microsoft 365. Excel 2016 and 2019 don't have it.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value- What to look for: a value or, more often, the cell that holds it.
lookup_array- The one column (or row) to search.
return_array- The column (or row) to return from. XLOOKUP returns the item at the same position as the match, so this range has to cover the same rows as
lookup_array. if_not_found- Optional. What to return when there is no match. Without it, a missing value gives
#N/A. match_mode- Optional.
0= exact match, the default.-1= exact match, or the next smaller item.1= exact match, or the next larger item.2= wildcard match with*,?and~. search_mode- Optional.
1= search from the first item, the default.-1= search from the last item.2and-2= binary search on data sorted ascending or descending.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Vendor | Category | Vendor ID | Net days | Find ID | Vendor | |
| 2 | Apex Paper | Supplies | V-204 | 30 | V-331 | Coastal Freight | |
| 3 | Blue Ridge IT | Services | V-117 | 45 | |||
| 4 | Coastal Freight | Shipping | V-331 | 15 | |||
| 5 | Delgado Electric | Repairs | V-142 | 20 | |||
| 6 | Evergreen Clean | Services | V-256 | 60 | |||
| 7 | Fulton Print | Supplies | V-309 | 10 |
A2:A7 to D2:D7 for the payment terms, or to B2:B7 for the category.How Excel reads it
- It takes the value in F2 (V-331) and goes down C2:C7 until it finds it: the third cell.
- It returns the third cell of A2:A7: Coastal Freight.
If the ID appears twice, XLOOKUP returns the first one it meets. The lookup column and the return column are two separate ranges, so their order on the sheet doesn't matter. Here the name is left of the ID, which VLOOKUP can't handle.
What changes compared with VLOOKUP
- Any direction. The return column can be on either side of the lookup column.
- Exact match by default. There is no
FALSEto remember. - No column number. You point at the column to return, so there is nothing to count, and nothing to recount when someone inserts a column.
- A built-in answer for "not found". The fourth argument replaces
#N/Awith a value you choose.
The two ways it goes wrong
Both are live below. Fix the formula in the box and watch the cell.
1. The value isn't in the lookup range. There is no vendor V-400, so the lookup ends in #N/A. That is the honest answer, but it spreads to every formula that uses this cell. To replace it, add a fourth argument: =XLOOKUP(F2,C2:C7,A2:A7,"No such vendor").
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Vendor | Category | Vendor ID | Net days | Find ID | Vendor | |
| 2 | Apex Paper | Supplies | V-204 | 30 | V-400 | #N/A | |
| 3 | Blue Ridge IT | Services | V-117 | 45 | |||
| 4 | Coastal Freight | Shipping | V-331 | 15 | |||
| 5 | Delgado Electric | Repairs | V-142 | 20 | |||
| 6 | Evergreen Clean | Services | V-256 | 60 | |||
| 7 | Fulton Print | Supplies | V-309 | 10 |
,"No such vendor".2. The two ranges don't line up. XLOOKUP returns the item at the same position as the match. If the return range starts one row lower than the lookup range, every answer comes from one row too low, and there is no error to warn you. Blue Ridge IT (V-117) is paid in 45 days. This formula says otherwise:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Vendor | Category | Vendor ID | Net days | Find ID | Net days | |
| 2 | Apex Paper | Supplies | V-204 | 30 | V-117 | 15 | |
| 3 | Blue Ridge IT | Services | V-117 | 45 | |||
| 4 | Coastal Freight | Shipping | V-331 | 15 | |||
| 5 | Delgado Electric | Repairs | V-142 | 20 | |||
| 6 | Evergreen Clean | Services | V-256 | 60 | |||
| 7 | Fulton Print | Supplies | V-309 | 10 |
D3:D8 to D2:D7 and the answer is 45.What XLOOKUP also does in Excel
The sheets on this page run exact matches that return one value. In Excel, XLOOKUP has more to offer:
- Next smaller or next larger. With
match_modeset to-1or1, a value that isn't found falls back to the next smaller or next larger item. That is the tool for brackets, like a tax rate by income. - Last match first.
search_mode-1searches from the bottom, which returns the last matching row instead of the first. - Several columns at once. When
return_arrayis more than one column wide, a single formula returns all of them for the matching row. - Rows as well as columns. The two ranges can be rows, which replaces HLOOKUP.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.
Someone types a vendor ID in F2. In G2, return that vendor's category.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Vendor | Category | Vendor ID | Net days | Find ID | Category | |
| 2 | Apex Paper | Supplies | V-204 | 30 | V-142 | ||
| 3 | Blue Ridge IT | Services | V-117 | 45 | |||
| 4 | Coastal Freight | Shipping | V-331 | 15 | |||
| 5 | Delgado Electric | Repairs | V-142 | 20 | |||
| 6 | Evergreen Clean | Services | V-256 | 60 | |||
| 7 | Fulton Print | Supplies | V-309 | 10 |
Show the answer
=XLOOKUP(F2,C2:C7,B2:B7) gives Repairs.
Look in the ID column (C2:C7), return from the category column (B2:B7). The category is left of the ID, and that makes no difference to XLOOKUP.
V-500 isn't in the list, so a plain XLOOKUP shows #N/A. In G2, return the vendor name for the ID in F2, or the text Not found when the ID doesn't exist.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Vendor | Category | Vendor ID | Net days | Find ID | Vendor | |
| 2 | Apex Paper | Supplies | V-204 | 30 | V-500 | ||
| 3 | Blue Ridge IT | Services | V-117 | 45 | |||
| 4 | Coastal Freight | Shipping | V-331 | 15 | |||
| 5 | Delgado Electric | Repairs | V-142 | 20 | |||
| 6 | Evergreen Clean | Services | V-256 | 60 | |||
| 7 | Fulton Print | Supplies | V-309 | 10 |
Show the answer
=XLOOKUP(F2,C2:C7,A2:A7,"Not found") gives Not found.
The fourth argument, if_not_found, is returned only when the lookup finds nothing. Wrapping the lookup in IFNA gives the same result.
Which vendor has to be paid soonest? In G2, return the name of the vendor with the smallest number in the Net days column.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Vendor | Category | Vendor ID | Net days | Pay first | ||
| 2 | Apex Paper | Supplies | V-204 | 30 | |||
| 3 | Blue Ridge IT | Services | V-117 | 45 | |||
| 4 | Coastal Freight | Shipping | V-331 | 15 | |||
| 5 | Delgado Electric | Repairs | V-142 | 20 | |||
| 6 | Evergreen Clean | Services | V-256 | 60 | |||
| 7 | Fulton Print | Supplies | V-309 | 10 |
Show the answer
=XLOOKUP(MIN(D2:D7),D2:D7,A2:A7) gives Fulton Print.
MIN(D2:D7) gives 10. XLOOKUP then finds 10 in the Net days column and returns the name from the same row. The lookup value can be any formula, not only a cell.
Questions people ask
What is an XLOOKUP in Excel?
A lookup function: it searches one column or row for a value and returns the item at the same position in another column or row. It does the job of VLOOKUP and HLOOKUP, with exact match as the default and an optional value to show when nothing is found.
XLOOKUP vs VLOOKUP: which one should I use?
If your Excel has it, XLOOKUP leaves less room for mistakes: no column number, no FALSE to forget, and it can return a column to the left. VLOOKUP also works in Excel 2016 and 2019, where XLOOKUP doesn't exist, so it is still what you'll find in older workbooks, and it's worth being able to read both.
Why does my XLOOKUP return #N/A?
Because the lookup value isn't in lookup_array exactly as written. A different spelling or a space at the end of one of the two values is enough. Check too that lookup_array is the column that holds those values. More causes, each with a formula to fix, are on the #N/A error page.
Does XLOOKUP work in Excel 2016 or 2019?
No. Microsoft's page says XLOOKUP is not available in Excel 2016 and Excel 2019. In those versions, INDEX with MATCH does the same lookups, including the ones to the left.