XLOOKUP in Excel
Last updated
XLOOKUP searches one range for a value and returns the matching item from another range. For example, =XLOOKUP(F2,A2:A9,D2:D9) finds the order number in F2 and returns its amount from column D. It uses exact match by default, can look to the left, and can show your own text instead of #N/A.
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value
- The value to find, such as an order number or a cell that holds it.
- lookup_array
- The single column (or row) to search, e.g.
A2:A9. - return_array
- The range to return from, e.g.
D2:D9. It must have as many rows as lookup_array and can sit to the left or right of it. Select several columns to return them all at once. - if_not_found
- Optional. What to show when nothing matches, e.g.
"Not found". Without it, XLOOKUP returns #N/A. - match_mode
- Optional.
0exact match (default),-1exact or next smaller,1exact or next larger,2wildcards (* and ?). - search_mode
- Optional.
1searches from the first item (default),-1from the last, which finds the most recent match.
Practice XLOOKUP here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Find an order’s amount
In F2, write an XLOOKUP that returns the amount of order 5024, using the orders in A1:D9.
| Order no. | Customer | City | Amount ($) | Amount of order 5024 ($) | |
| 5031 | Bluebird Café | Boston | 412.50 | ||
| 5007 | Harbor Books | Denver | 86.00 | ||
| 5024 | Maple Dental | Austin | 1,290.00 | ||
| 5012 | Summit Gym | Seattle | 245.00 | ||
| 5040 | Pine Street Bakery | Chicago | 37.90 | ||
| 5019 | Copper Kettle Deli | Portland | 768.00 | ||
| 5003 | Riverside Garage | Miami | 159.00 | ||
| 5028 | Oak & Ivy Florist | Phoenix | 532.00 |
How to use XLOOKUP, step by step
- Click the cell where you want the result and type
=XLOOKUP(. - Click the cell that holds the value to find, then type a comma.
- Select the column to search, type a comma, then select the column to return. Press F4 on each range to lock it with $ if you will copy the formula.
- Close the parenthesis and press Enter. Add a 4th argument in quotes if you want a message instead of #N/A.
XLOOKUP examples
Amount from an order number
=XLOOKUP(F2,$A$2:$A$9,$D$2:$D$9)
Returns the amount of the order in F2. No column number and no FALSE needed.
Look to the left
=XLOOKUP(F2,$B$2:$B$9,$A$2:$A$9)
Searches the customers in column B and returns the order number from column A, to the left.
Show a message instead of #N/A
=XLOOKUP(F2,$A$2:$A$9,$D$2:$D$9,"Not found")
The 4th argument replaces #N/A when the order doesn’t exist.
Commission by sales band
=XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,,-1)
With match_mode -1, XLOOKUP returns the largest band that is less than or equal to D2. The two commas skip if_not_found.
Common XLOOKUP errors and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #N/A | The value isn’t in lookup_array: a typo, extra spaces, or a number stored as text. Or the ranges moved when you copied the formula. | Check the value, use TRIM, lock the ranges with $, or add an if_not_found argument. |
| #VALUE! | lookup_array and return_array don’t have the same number of rows, e.g. A2:A9 and D2:D8. | Make both ranges start and end on the same rows. |
| #NAME? | Your version of Excel doesn’t have XLOOKUP (Excel 2019 and earlier), or the function name is misspelled. | Use Excel 2021, 2024 or Microsoft 365, or fall back on VLOOKUP or INDEX and MATCH. |
| #SPILL! | return_array has several columns, and the cells where the results spill aren’t empty. | Clear the cells to the right of the formula, or return a single column. |
XLOOKUP vs VLOOKUP
XLOOKUP replaces most VLOOKUPs: you select the return column instead of counting its number, exact match is the default, it can look to the left, and inserting a column doesn’t break it. VLOOKUP still works in every version of Excel, including 2019 and earlier where XLOOKUP returns #NAME?, so you’ll keep meeting it in shared files.
Frequently asked questions
What is the difference between XLOOKUP and VLOOKUP?
VLOOKUP needs a column number, searches only the first column and returns columns to its right. XLOOKUP takes separate lookup and return ranges, so it can look left, and it matches exactly by default.
Why does XLOOKUP show #NAME?
XLOOKUP exists only in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. In Excel 2019 or earlier, the name isn’t recognized. Check the spelling too.
How do I find the last match with XLOOKUP?
Set search_mode to -1 so the search starts from the bottom: =XLOOKUP(F2,A2:A9,D2:D9,,0,-1).
Can XLOOKUP return several columns?
Yes. Select several columns as return_array, e.g. B2:D9, and the results spill into the cells to the right.
Keep practising
All 10 XLOOKUP exercises, with explanations and progress tracking.
Open the XLOOKUP practice set