=XLOOKUP(F2,A2:A6,C2:C6) looks for the value in F2 in A2:A6 and returns the value from the same row of C2:C6. It looks for an exact match by default, the search column can be anywhere, and it needs Excel 2021 or Microsoft 365 (in Excel 2019 and older use INDEX and MATCH). Type another product in F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Bread | $2.40 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Click G2: the search range and the return range are outlined separately. Change C2:C6 to B2:B6 and G2 returns the category. There is no column number to count, so inserting a column between A and C does not break the formula: Excel moves both ranges.
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | What it does | Default |
|---|---|---|
lookup_value | The value to find. | required |
lookup_array | The column (or row) to search. | required |
return_array | The column, row or block to return from. Same height as lookup_array. | required |
if_not_found | What to show when nothing matches. | #N/A |
match_mode | 0 exact, -1 exact or next smaller, 1 exact or next larger, 2 wildcards. | 0 |
search_mode | 1 first to last, -1 last to first, 2 and -2 binary search on sorted data. | 1 |
Only the first three are required. To skip an optional argument and set a later one, leave it empty between commas: =XLOOKUP(F2,A2:A6,C2:C6,,0,-1) sets search_mode and leaves if_not_found at its default.
Return several columns at once
Give XLOOKUP a return range several columns wide and the whole row comes back. The result spills into the cells beside the formula.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Price | Stock |
| 2 | Apple | Fruit | $1.20 | 40 |
| 3 | Pear | Fruit | $1.50 | 25 |
| 4 | Carrot | Vegetable | $0.80 | 60 |
| 5 | Bread | Bakery | $2.40 | 15 |
| 6 | Milk | Dairy | $1.10 | 30 |
| 7 | Look for | Carrot | ||
| 8 | Result | Vegetable | $0.80 | 60 |
One formula in B8 fills B8:D8 with Vegetable, $0.80 and 60. Type something into C8 and B8 shows #SPILL!, because the result has no room; delete it and the result comes back. To return the columns in another order, wrap the return range in CHOOSECOLS: =XLOOKUP(B7,A2:A6,CHOOSECOLS(B2:D6,3,1)) gives Stock, then Category.
XLOOKUP to the left, and a message when nothing matches
The search column does not have to come first. Here XLOOKUP searches the prices in column C and returns the product name from column A, which VLOOKUP cannot do. The fourth argument says what to show when no product has that price.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | Product | |
| 2 | Apple | Fruit | $1.20 | 40 | $2.40 | Bread | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
$2.40 returns Bread. Change F2 to 3 and G2 shows "No product" instead of #N/A. "" as the fourth argument shows an empty-looking cell. if_not_found only covers "not found": a return range of the wrong height still gives #VALUE!, which is what you want to see.
Find the last match
XLOOKUP returns the first match from the top. Set search_mode, the sixth argument, to -1 and it searches from the bottom, so it returns the last match: the latest order, the most recent price, the last status.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | First | Last | |
| 2 | 2026-03-02 | Ben | 120 | Ben | 120 | 60 | |
| 3 | 2026-03-05 | Ana | 80 | ||||
| 4 | 2026-03-09 | Ben | 45 | ||||
| 5 | 2026-03-12 | Cara | 200 | ||||
| 6 | 2026-03-20 | Ben | 60 | ||||
| 7 | 2026-03-24 | Ana | 95 |
Ben's first order is 120 and his last is 60. Change E2 to Ana: 80 and 95. This relies on the rows being in date order. If they are not, look up the customer's latest date instead: =XLOOKUP(1,(B2:B7=E2)*(A2:A7=MAXIFS(A2:A7,B2:B7,E2)),C2:C7).
Approximate match: next smaller or next larger
match_mode -1 returns an exact match or, if there is none, the next smaller value. That is the rule for bands: a commission tier, a tax bracket, a grade. Unlike VLOOKUP with TRUE, the table does not have to be sorted. The bands below are in no particular order on purpose.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 5000 | 5% | Ana | 750 | 0% | |
| 3 | 0 | 0% | Ben | 4,200 | 3% | |
| 4 | 10000 | 8% | Cara | 5,000 | 5% | |
| 5 | 1000 | 3% | Dev | 12,500 | 8% |
Ben's 4,200 falls between 1,000 and 5,000, so he gets the 1,000 band's 3%. Cara's 5,000 is an exact match, 5%. match_mode 1 works the other way, exact or next larger, which answers "the smallest box that fits" or "the next delivery slot": =XLOOKUP(18,{5;12;25;50},{"S";"M";"L";"XL"},,1) returns L.
XLOOKUP with wildcards
match_mode 2 turns * (any characters) and ? (one character) into wildcards. Without it XLOOKUP looks for the characters themselves, which is the opposite of VLOOKUP (whose exact match accepts wildcards) and the usual reason a wildcard XLOOKUP returns #N/A or its if_not_found text:
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None") None: no product is named *coffee*
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None",2) 2.9, the price of Iced coffee
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
"coffee" finds Iced coffee first, 8.50. Like every Excel lookup, the match ignores case. To find a real asterisk or question mark in match_mode 2, put a tilde before it: "~*".
Two-way XLOOKUP
An XLOOKUP that returns a whole row can be the return range of a second XLOOKUP. The inner one picks the row by region, the outer one picks the month's column from that row.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | South | ||
| 7 | Month | Feb | ||
| 8 | Sales | 3,600 |
XLOOKUP(B6,A2:A5,B2:D5) returns South's row, 3100, 3600 and 3300. The outer XLOOKUP finds Feb in B1:D1 and takes the matching value from that row: 3,600. Pick another region and month in B6 and B7. The INDEX and MATCH version of the same lookup is on the INDEX and MATCH page.
XLOOKUP in older Excel and Google Sheets
XLOOKUP exists in Excel 2021, Excel 2024, Microsoft 365, Excel for the web and the mobile apps. If you open a file that uses it in Excel 2019 or older, the formulas show #NAME? as soon as they recalculate. When a file must work everywhere, write the lookup with INDEX and MATCH, which every version understands:
=XLOOKUP(F2, A2:A6, C2:C6, "Not found")
=IFNA(INDEX(C2:C6, MATCH(F2, A2:A6, 0)), "Not found")
Google Sheets has had XLOOKUP since 2022, with the same arguments. For a side by side of the differences, see VLOOKUP vs XLOOKUP. To match on two columns at once (product and size, name and date), the pattern =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6) is explained on the lookup with multiple criteria page.
Practice: a price, or "Not found"
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | ||
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Your turn: In G2, return the price of the product in F2, or the text Not found when it is not in the list.
Practice: discount by order size
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order from | Discount | Order | Discount | |
| 2 | $0 | 0% | $320 | ||
| 3 | $100 | 5% | |||
| 4 | $250 | 10% | |||
| 5 | $500 | 15% |
Your turn: Each discount applies from its order amount up. In E2, use XLOOKUP to return the discount for the order amount in D2.
Frequently Asked Questions
How do I use XLOOKUP in Excel?
Give it three arguments: what to find, the column to search, and the column to return. =XLOOKUP("Pear",A2:A6,C2:C6) finds Pear in A2:A6 and returns the value from the same row of C2:C6. It looks for an exact match unless you say otherwise.
Which Excel versions have XLOOKUP?
Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. In Excel 2019 and older the formula shows #NAME?; use =INDEX(C2:C6,MATCH(F2,A2:A6,0)) there. Google Sheets also has XLOOKUP.
How do I make XLOOKUP return a blank or text instead of #N/A?
Use the fourth argument, if_not_found: =XLOOKUP(F2,A2:A6,C2:C6,"Not found"), or "" for an empty-looking cell. It only replaces the not-found case; other errors still show.
How do I find the last match with XLOOKUP?
Set the sixth argument, search_mode, to -1 so the search runs from the bottom up: =XLOOKUP("Ben",B2:B7,C2:C7,,0,-1) returns Ben's last amount instead of his first.
Can XLOOKUP return more than one column?
Yes. Give it a return range several columns wide, such as =XLOOKUP(F2,A2:A6,B2:D6), and the result spills into the cells to the side. The cells it spills into must be empty, or Excel shows #SPILL!.
