An XLOOKUP generator writes the formula from what you want to find, where to look, what to bring back, and how to match and search. A common job: an order sheet needs each product's current price from a Prices sheet where every price change is added as a new row, so a code can appear four times and the price you want is the last one. Type A2, Prices!A2:A500 and Prices!C2:C500, pick "Last to first", and the generator gives you =XLOOKUP(A2,Prices!$A$2:$A$500,Prices!$C$2:$C$500,,0,-1).
How to use the XLOOKUP generator
- Type the lookup value. Usually a cell such as
A2. A number works as it is, and text gets its quotes added.
- Type the lookup array (the one column or row to search, like
Products!A2:A100) and the return array (what to bring back, like Products!C2:C100). "Lock both ranges with $" is ticked by default. Leave it on if you'll fill the formula down.
- If you want something other than #N/A when nothing matches, type it in the if-not-found field.
- Pick a match mode and a search mode. Exact match and first to last are the defaults.
- Read the formula and the line under it for each argument, then click "Copy formula". Paste it into the first result cell and fill down.
The formula is built in your browser as you type. Nothing is uploaded, and there's no sign-up or usage limit.
What the XLOOKUP generator writes
It leaves off trailing arguments that are at their defaults, the way you'd write XLOOKUP by hand. When a later argument isn't at its default, the ones before it are written out, and an unused if_not_found is left as an empty slot so the positions still line up.
| Your choices | The formula |
|---|
| All defaults | =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100) |
| If not found: Not found | =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found") |
| Exact or next smaller, no if_not_found | =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,,-1) |
| Last to first, with Not found | =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found",0,-1) |
Return array Products!B2:D100 | =XLOOKUP(A2,Products!$A$2:$A$100,Products!$B$2:$D$100), spilling across three cells |
The line for each argument covers the ones left out too, such as "No if_not_found, so no match shows #N/A." Text you type as the lookup value or fallback gets quotes, with any quote inside it doubled. Start with = to pass a defined name or expression through as written.
The return array can sit to the left of the lookup array. With codes in column C and names in column A, search Products!C2:C100 and return Products!A2:A100. VLOOKUP can't look left like that.
We checked the output in Excel for Mac against a small test table: exact match returned the right value, last to first found the second of two Widget rows, the if_not_found text showed for a missing code, and a three-column return spilled across three cells.
For the XLOOKUP last match, -1 goes in the sixth argument
XLOOKUP has two optional arguments that both take -1, and they sit next to each other. The fifth is match_mode, where -1 means "exact match or next smaller". The sixth is search_mode, where -1 means "search from the bottom up".
Put the -1 in the fifth slot by mistake, =XLOOKUP(A2,Prices!$A$2:$A$500,Prices!$C$2:$C$500,,-1), and you get the first price for each code, not the latest. Worse, a code that isn't in the list no longer returns #N/A. It returns the row with the next smaller value, and with text codes that means the one immediately before it in alphabetical order. We tested this in Excel: next smaller on "Nope" returned Gadget's row. No error appears, so the wrong price looks like a right one.
The generator fills the slots from the two dropdowns, so last to first always lands in sixth position, with the match mode written out in front of it.
What XLOOKUP if_not_found doesn't catch
if_not_found fires only when nothing matches. Two cases slip past it, and a third hides behind it.
A match with an empty cell. If the code is found but its price cell is blank, XLOOKUP returns 0, not your fallback. =LET(x,XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100),IF(x="","",x)) shows a blank instead.
Other errors. Unlike IFERROR, if_not_found leaves every other error alone, including #VALUE!. That's usually what you want: a broken range still shows as broken.
Values that look equal but aren't. Here if_not_found does fire, and that's the problem: the sheet shows "Not found" for a code you can see in the list. Numbers stored as text and trailing spaces cause most of these. Codes pulled from another system often carry a line break too, which is invisible in the cell. =LEN(A2) counts it, =A2=Products!A5 returns FALSE when two cells only look the same, and CLEAN in a helper column strips the line break. Keys that come out of a calculation can differ in the fifteenth decimal place, so wrap the formulas that produce them in ROUND.
Leaving if_not_found blank in the generator leaves the argument out, so no match shows #N/A. An empty string (type ="") hides the #N/A, but it's text: =E2*F2 on that cell returns #VALUE!. Use 0 as the fallback if the column feeds a calculation.
XLOOKUP match modes, and when sorting matters
Exact or next smaller (-1) and exact or next larger (1) don't need sorted data in XLOOKUP. That's the big difference from VLOOKUP's approximate match. With shipping bands of 0, 1, 5 and 20 kg in any order, =XLOOKUP(3.2,Rates!$A$2:$A$5,Rates!$B$2:$B$5,,-1) returns the 1 kg rate. These modes are meant for numbers and dates: on a text value they compare alphabetically. The generator warns you when the lookup value you typed is text, but it can't see what's in a cell like A2.
Sorting matters only for binary search (search modes 2 and -2). On a list that isn't sorted in that order, binary search returns wrong results with no error, and the generator shows that warning when you pick it. On a few thousand rows the default search is fast enough, so leave binary search alone unless you know the list is sorted and have a reason.
Wildcard mode (2) treats * as any run of characters and ? as one character, so "Wid*" finds Widget. Put ~ before either to find a literal * or ?.
Why XLOOKUP returns #VALUE! or #SPILL!
#VALUE! usually means the lookup array and return array are different sizes. The generator checks this while you type: Products!A2:A100 against Products!C2:C50 gets "The lookup array has 99 rows and the return array 49." It also rejects a lookup array that's a block, like A2:B100, since XLOOKUP searches one column or one row. Whole columns such as A:A and C:C pass. Defined names and table references can't be measured, so they go through unchecked.
#SPILL! comes from a return array more than one column wide when the cells to the right aren't empty. The generator warns you to leave room. Spilling is also blocked inside an Excel table, so put a multi-column XLOOKUP outside it.
Writing XLOOKUP in Excel without the generator
Formulas > Lookup & Reference > XLOOKUP opens the Function Arguments dialog (the Formula Builder on a Mac) with all six boxes, and you can drag across each range instead of typing it. It won't add $ signs: click into each range in the formula bar and press F4 (Cmd+T on a Mac). That route suits you if you'd rather point at the data, and it's the only one for a multiple-criteria lookup.
XLOOKUP needs Microsoft 365 or Excel 2021 or later. Open the workbook in Excel 2019 or earlier and the formula turns into _xlfn.XLOOKUP with #NAME? in the cell. If anyone who opens the file is on an older version, use the VLOOKUP generator instead.
What the generator can't do
- It builds the formula without reading your data, so only Excel can tell you whether a code is in the list.
- It separates arguments with commas. If your Excel uses semicolons, as many European setups do, swap the commas between arguments for semicolons.
- It doesn't write a two-way lookup (one XLOOKUP nested in another) or a multiple-criteria lookup.
- It can't check the size of a defined name or table reference.
- XLOOKUP itself returns one match. To get every row for a code, use FILTER.
The XLOOKUP function page goes through each argument with worked examples, and the VLOOKUP function page covers the older function for comparison.
Questions
How do I make XLOOKUP return a blank instead of #N/A?
Put an empty string in the fourth argument, if_not_found: =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,""). In the generator, type ="" in the if-not-found field, because a leading = passes it through as written. The empty string is text, so arithmetic on that cell returns #VALUE!. If the column feeds a sum, type 0 instead.
Why does XLOOKUP return 0 instead of a blank?
Because it found a match and the matching cell in the return array is empty. if_not_found only covers the case where nothing matches, so it never fires here. Wrap the formula as =LET(x,XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100),IF(x="","",x)) to show a blank instead.
How do I find the last match with XLOOKUP?
Set search_mode, the sixth argument, to -1: =XLOOKUP(A2,Prices!$A$2:$A$500,Prices!$C$2:$C$500,,0,-1). XLOOKUP then searches from the bottom up and stops at the last row that matches. A -1 in the fifth argument does something else entirely: it changes the match mode to exact or next smaller and still returns the first match.
What is the difference between XLOOKUP match_mode -1 and VLOOKUP's TRUE?
Both return the next smaller value when there is no exact match, but VLOOKUP's TRUE needs the first column sorted smallest to largest and XLOOKUP's -1 does not. VLOOKUP also falls back to approximate match when you leave its fourth argument off, while XLOOKUP defaults to exact match. Sorting only matters in XLOOKUP when you pick binary search as the search mode.
Why does my XLOOKUP show as _xlfn.XLOOKUP?
The workbook was opened in a version of Excel that doesn't have XLOOKUP, such as Excel 2019, 2016 or 2013. Excel adds the _xlfn. prefix to a function it doesn't recognise and the cell shows #NAME?. Either open the file in Microsoft 365 or Excel 2021 or later, or rewrite the lookup as VLOOKUP or INDEX and MATCH.
Can XLOOKUP return more than one column at once?
Yes. Give it a return array several columns wide, such as Products!$B$2:$D$100, and the matching row spills into the cells to the right of the formula. Those cells have to be empty or Excel shows #SPILL!. Spilling isn't allowed inside an Excel table, so a multi-column XLOOKUP has to sit outside one.
How do I use XLOOKUP with multiple criteria?
Look up 1 in an array built by multiplying the conditions together: =XLOOKUP(1,(A2:A20=E2)*(B2:B20=F2),C2:C20). Each comparison returns TRUE or FALSE, the multiplication turns a row where both are TRUE into 1, and XLOOKUP returns that row. The generator can't build this one, so type it into Excel directly.