XLOOKUP in Excel: How to Use It (With Examples)
· 7 min read
XLOOKUP finds a value in one column and returns the matching value from another column. You give it three things: what to find, where to look, and what to return. It does an exact match by default, so there is no FALSE to remember:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Only the first three arguments are required. XLOOKUP works in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. In Excel 2019 and older it returns #NAME?, so check which version your readers have before you send the file.
The example table
A supplies company keeps a price list. Codes are in column A, prices in column D. The codes to look up are in F2 and F3, and the formulas from this guide are in column H.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Code | Product | Category | Price | Find | Formula | Result | |
| 2 | SKU-201 | Sticky notes | Office | 3.20 | SKU-204 | Basic lookup of F2 | 62.40 | |
| 3 | SKU-202 | Paper towels | Cleaning | 14.75 | SKU-209 | F3 with if_not_found | Not in price list | |
| 4 | SKU-203 | Mug set | Kitchen | 18.00 | AI's formula on F3 | FALSE | ||
| 5 | SKU-204 | Toner cartridge | Office | 62.40 | Fixed: no 4th argument | #N/A | ||
| 6 | SKU-205 | Glass cleaner | Cleaning | 5.95 |
How to use XLOOKUP: a basic lookup
Type SKU-204 in F2. To get its price:
=XLOOKUP(F2, A2:A6, D2:D6)Result: 62.40. Read it out loud and it makes sense: find F2, in the codes A2:A6, and return the matching cell from the prices D2:D6.
Three things to notice:
- The lookup array and the return array must be the same height. Both run from row 2 to row 6 here.
- The return column can be anywhere: left of the codes, right of them, even on another sheet. You never count columns.
- If someone inserts a column between A and D, the formula updates itself and still returns the price.
Filling it down an order list
Most real lookups run on a list of many rows. Say your order lines have product codes in H2:H50. In I2 you write:
=XLOOKUP(H2, $A$2:$A$6, $D$2:$D$6)Then fill it down. The $ signs lock the price list so it stays put, while H2 moves to H3, H4 and so on. Without them, the price list slides down one row per copy and the lower rows start returning #N/A. Our guide to absolute references explains the $ signs in detail.
XLOOKUP if not found
When the code is not in the list, XLOOKUP returns #N/A. That is often what you want, because it tells you something is missing. When you would rather show a message, use the fourth argument. With SKU-209 in F3:
=XLOOKUP(F3, A2:A6, D2:D6, "Not in price list")Result: Not in price list (H3 in the sheet above). This replaces the old IFERROR(VLOOKUP(...), ...) wrapper, and it is safer. IFERROR hides every kind of error, including a typo in your formula. The if_not_found argument only kicks in when the value really is not found. More on that in IFERROR in Excel.
Choose the message with care. Returning 0 for a missing price makes the order look free, and a total built on top of it will be quietly too low. A text message or a blank "" is usually easier to spot.
XLOOKUP return multiple columns
Point the return array at more than one column and XLOOKUP returns all of them. With SKU-204 in F2, type this in an empty cell, say J2:
=XLOOKUP(F2, A2:A6, B2:D6)The result spills across three cells:
| J | K | L | |
|---|---|---|---|
| 2 | Toner cartridge | Office | 62.40 |
You only type the formula in J2. K2 and L2 fill themselves. If any of those cells already contains something, you get #SPILL! instead. Clear the cells and the result appears.
The columns come back in the same order as the sheet. If you need columns that are not next to each other, the simplest fix is one XLOOKUP per column.
Find the last match instead of the first
XLOOKUP searches from the top by default and stops at the first match. Its sixth argument, search_mode, can reverse that. This is handy for logs where the newest row is at the bottom. Here is a price change log, sorted by date:
| A: Date | B: Code | C: New price | |
|---|---|---|---|
| 2 | 2025-01-06 | SKU-202 | 13.90 |
| 3 | 2025-03-03 | SKU-205 | 5.60 |
| 4 | 2025-04-14 | SKU-202 | 14.25 |
| 5 | 2025-06-02 | SKU-205 | 5.95 |
| 6 | 2025-08-18 | SKU-202 | 14.75 |
=XLOOKUP("SKU-202", B2:B6, C2:C6, , 0, -1)Result: 14.75, the latest price. The empty fourth argument keeps the default #N/A, 0 is exact match, and -1 means "search from last to first". Without the last two arguments the same formula returns 13.90, the oldest price.
Other match modes
The fifth argument, match_mode, has four options:
| match_mode | Meaning |
|---|---|
| 0 | Exact match (the default) |
| -1 | Exact match, or the next smaller item |
| 1 | Exact match, or the next larger item |
| 2 | Wildcard match, where * means any characters |
Wildcards help when you only know part of a name:
=XLOOKUP("*toner*", B2:B6, D2:D6, , 2)Result: 62.40. Matching ignores upper and lower case, so "toner" finds "Toner cartridge".
Modes -1 and 1 are for bands and tiers, like discount levels by order size. Our VLOOKUP vs XLOOKUP comparison has a worked example, plus a lookup with two criteria.
Common mistakes
Carrying VLOOKUP habits into XLOOKUP. This is one AI assistants make. Asked for an exact lookup, a chatbot may write =XLOOKUP(F2, A2:A6, D2:D6, FALSE), adding FALSE the way VLOOKUP needs it. In XLOOKUP the fourth argument is if_not_found, so a missing code now shows FALSE instead of #N/A. Compare H4 and H5 in the sheet at the top: same lookup, but only the fixed version tells you SKU-209 is missing. The formula still works for codes that exist, which is why nobody notices. When an AI hands you an XLOOKUP, read every argument after the third and ask what each one does.
Ranges of different heights. =XLOOKUP(F2, A2:A6, D2:D7) returns #VALUE!. The lookup and return arrays must cover the same rows.
Sending the file to someone on an old version. In Excel 2019 or earlier the formula shows #NAME?, and the formula bar may show it as _xlfn.XLOOKUP. If the file must work there, use INDEX MATCH instead. See how to use INDEX MATCH.
Unlocked ranges when filling down. The lookup works in the first row and then breaks a few rows later. Click the last filled cell and check that the ranges still point at A2:A6 and D2:D6.
Values that look equal but are not. A trailing space in "SKU-204 " or a number stored as text will not match. If one row returns #N/A and its neighbours work, check the length of both values with =LEN(F2).
FAQ
Is XLOOKUP Excel 365 only?
No. XLOOKUP is in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. It is not in Excel 2019, 2016 or older versions.
How do I use XLOOKUP if not found?
Put the value you want as the fourth argument, for example =XLOOKUP(F3, A2:A6, D2:D6, "Not in price list"). Leave it out to keep the standard #N/A.
Can XLOOKUP return multiple columns?
Yes. Make the return array several columns wide, such as B2:D6, and the results spill into the cells to the right. Make sure those cells are empty.
Is XLOOKUP better than VLOOKUP?
For most work, yes: exact match by default, it can return columns to the left, and it survives inserted columns. VLOOKUP still matters when a file has to open in older versions.
Why does my XLOOKUP return #N/A?
The value is not in the lookup array exactly as typed. Check for extra spaces, numbers stored as text, or a lookup array that does not cover every row.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Who manages each order?” →