The XLOOKUP function
Finds a value in one range and returns the matching item from another. Exact match by default, with an optional value for not found.
How to use XLOOKUP
XLOOKUP is the modern lookup. You tell it what to find, which range to search and which range to bring the answer from. Because the search and return ranges are separate, the answer can sit to the left or right of the key, or on another sheet entirely.
Use it for the same jobs as VLOOKUP: a price from a price list, a department from an employee number, a customer name from an ID. It also handles cases VLOOKUP struggles with. The fourth argument is what to show when nothing matches, so =XLOOKUP(A2,F2:F20,G2:G20,"Not found") needs no IFERROR. Match mode finds the next smaller value for discount tiers or tax brackets, or turns on wildcards. Search mode -1 starts from the bottom, which gives you the last entry when a customer appears several times.
Both ranges must be the same size. XLOOKUP matches exactly unless you say otherwise, so there is no FALSE to forget, and inserting a column does not break it. To return several columns at once, point return_array at all of them and the results spill into the cells to the right. If you need a classic alternative, INDEX with MATCH does the same job.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value
- The value to find.
- lookup_array
- The range to search.
- return_array
- The range to return from, the same size as lookup_array.
- if_not_found (optional)
- What to return when there is no match, instead of #N/A.
- match_mode (optional)
- 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcards.
- search_mode (optional)
- 1 first to last (default), -1 last to first.
Examples
=XLOOKUP(1"Plum",2A2:A4,3B2:B4)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 5 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: Find 1Plum in 2the fruit in A2:A4 and return 3its price from B2:B4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | Not found | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
=XLOOKUP(11,2(A2:A5="North")*(B2:B5="Bags"),3C2:C5)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 150 | |
| 2 | North | Shoes | 420 | ||
| 3 | South | Bags | 310 | ||
| 4 | North | Bags | 150 | ||
| 5 | East | Shoes | 280 |
In plain words: Find 1a 1 in 2the rows where the region is North and the product is Bags and return 3the sales from C2:C5.
Common XLOOKUP mistakes
XLOOKUP returns #N/A for a value that is clearly in the list
The values only look the same. A stray space or a number stored as text is the usual cause. Clean the lookup value with
=XLOOKUP(TRIM(D2),A2:A20,B2:B20), or useVALUE(D2)when the list holds real numbers and your value is text.XLOOKUP always finds the first row, but I want the last
When a value appears more than once, XLOOKUP returns the first match from the top. Set search mode to -1 to search from the bottom:
=XLOOKUP(E2,A2:A20,C2:C20,"Not found",0,-1). In a list sorted by date, that gives the most recent row.XLOOKUP misses the discount when the amount falls between two tiers
The default is an exact match. Set match mode to -1 to get the next smaller tier. The table does not need to be sorted:
=XLOOKUP(B2,E2:E5,F2:F5,0,-1).XLOOKUP gives #VALUE!
The lookup range and the return range have different sizes, such as
A2:A6andC2:C7. Make both cover the same rows:=XLOOKUP(E2,A2:A6,C2:C6).
Practice XLOOKUP
Read the guide
- Excel test for a job interview: what it looks like and how to practice
- VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use
- Excel for finance and accounting: the skills you actually use
- Google Sheets vs Excel: what is the same, what is different, and how to switch
- How to create a drop-down list in Excel, and make it update itself
- Excel inventory data: free practice file with tasks and answers
- Excel customer data: free practice file with tasks and answers
- Excel finance data: free practice file with tasks and answers
Questions
Why is my XLOOKUP not working?
#NAME? means your Excel doesn't have XLOOKUP, which needs Microsoft 365 or Excel 2021 or later. #VALUE! usually means the lookup and return ranges have different sizes, such as A2:A6 and C2:C7. #N/A means there is no match, so check for extra spaces and numbers stored as text, or give a fourth argument with your own text.
How do I use XLOOKUP with multiple criteria?
Look up 1 in the product of the tests, =XLOOKUP(1,(A2:A6=F1)*(B2:B6=F2),C2:C6). Each test gives TRUE or FALSE per row, multiplying them gives 1 only where both are true, and XLOOKUP returns that row.
Is XLOOKUP better than VLOOKUP?
In most cases, yes. XLOOKUP looks in any direction, matches exactly by default, doesn't break when you insert a column and has a built-in value for not found. Use VLOOKUP when the file must work in Excel 2019 or older.
Which Excel versions have XLOOKUP?
Excel for Microsoft 365, Excel 2021 and later, Excel for the web and Google Sheets. Excel 2019 and older do not.
How do I return multiple columns with XLOOKUP?
Point the return range at several columns, as in =XLOOKUP(A2,F2:F20,G2:I20). The three results spill into the cells to the right, so those cells must be empty or you get #SPILL!.