Excel guide · 4 min read

XLOOKUP if not found: return a blank, 0 or text

Q: How do I make XLOOKUP return blank instead of #N/A?

Put "" as the fourth argument: =XLOOKUP(value,lookup_range,return_range,""). The cell then looks empty when the value is missing. It holds empty text, so do not multiply it.

Q: Can if_not_found run another lookup?

Yes. Any formula works in the fourth argument, so =XLOOKUP(x,list1,prices1,XLOOKUP(x,list2,prices2,"Not found")) searches a second table only when the first has no match.

Quick answerPut what you want to show in XLOOKUP's fourth argument: =XLOOKUP(I2,A2:A8,C2:C8,"Not found") shows Not found, ,"") shows a blank and ,0) shows zero. It replaces #N/A only when the value is missing, so you do not need IFERROR or IFNA around XLOOKUP.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	F	G	I
1	SKU	Product	Price	In stock	Old SKU	Old price	Look up
2	PT-1001	Ceramic planter, small	14.5	32	PT-0990	12	PT-1003
3	PT-1002	Ceramic planter, large	29	11	PT-0995	26.5	PT-1004
4	PT-1004	Hanging macrame holder	18.75				PT-0995
5	PT-1007	Self-watering pot	22	8			PT-1012
6	PT-1009	Terracotta set of 3	19.99	0
7	PT-1012	Plant stand, oak	64	5
8	PT-1015	Watering can, brass	38	14

Download this sheet as an Excel practice file (.xlsx)

The if_not_found argument

XLOOKUP takes up to six arguments: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The fourth one is what XLOOKUP returns when the lookup value is not in the lookup array. Leave it out and you get #N/A.

Here a customer asks for SKU PT-1003, which the price list does not have. XLOOKUP is in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. In Excel 2019 and earlier, use =IFNA(VLOOKUP(I2,A2:C8,3,FALSE),"Not found") instead.

=XLOOKUP(I2,A2:A8,C2:C8,"Not found")

Result: Not found

Not found instead of #N/A, because PT-1003 is not in the SKU column.

Return a blank (and what that blank really is)

Two double quotes with nothing between them return an empty text string, so the cell looks empty. This is the tidy choice for a report or an invoice, where a word like "Not found" would clutter the page.

The catch: "" is text, not an empty cell. ISBLANK says FALSE, COUNTBLANK counts it, and any arithmetic on it fails. If another formula multiplies this result by a quantity, you get #VALUE! instead of a price.

=XLOOKUP(I2,A2:A8,C2:C8,"")*2

Result: #VALUE!

#VALUE!: the lookup returned "" and Excel cannot multiply text. Return 0 instead when the result feeds a calculation.

Return 0 when the result feeds a calculation

If the looked-up price is multiplied, summed or compared, return 0 for missing items. The line total is then 0 rather than an error, and totals further down the sheet keep working.

Pick 0 only when zero is a sensible answer. A missing price that silently turns into a free item is a costly mistake on an order form, so pair it with a visible check such as a "Not found" flag in the next column.

=XLOOKUP(I2,A2:A8,C2:C8,0)*3

Result: 0

0: three units of a product that is not on the list cost nothing instead of breaking the total.

The trap: a found but empty cell returns 0

PT-1004 is on the list, but its In stock cell is empty. XLOOKUP finds the row, so if_not_found does not apply, and Excel shows an empty result cell as 0. That reads as "out of stock", which is not what the blank means.

Test the result with ="" and show your own label. The comparison is TRUE for an empty cell, so you can tell "found but empty" apart from a real 0 like PT-1009's. Avoid the popular &"" fix if you need numbers: it turns 32 into the text "32".

=IF(XLOOKUP(I3,A2:A8,D2:D8)="","Ask supplier",XLOOKUP(I3,A2:A8,D2:D8))

Result: Ask supplier

Ask supplier for PT-1004, while =XLOOKUP(I3,A2:A8,D2:D8,"Not found") would have shown 0.

Fall back to a second list

The fourth argument can be another formula, including a second XLOOKUP. Excel searches the current price list first and only looks in the old list when the SKU is missing. Nest a third list the same way, and end the chain with a message.

PT-0995 was discontinued, so it only exists in the old price list in F2:G3.

=XLOOKUP(I4,A2:A8,C2:C8,XLOOKUP(I4,F2:F3,G2:G3,"Not found"))

Result: 26.5

26.5, the old price, found in the second list after the first search came up empty.

When "Not found" hides a real match

A friendly message can hide bad data. I5 holds "PT-1012 " with a trailing space, pasted from an email. The plant stand is on the list, but XLOOKUP needs an exact match and reports Not found. Wrap the lookup value in TRIM to strip the stray spaces.

The same thing happens when one side is a number and the other is text, such as 1012 looked up in a column of "1012" text codes. If items you can see on the list keep coming back Not found, check for spaces and number formats before trusting the message.

=XLOOKUP(TRIM(I5),A2:A8,C2:C8,"Not found")

Result: 64

64, the price of PT-1012, once the trailing space is removed. Without TRIM the result is Not found.

Common mistakes

Wrapping XLOOKUP in IFERROR

IFERROR also hides #VALUE!, #REF! and typos in your formula. Use the fourth argument, which only handles "not found".

Lookup and return ranges of different sizes

XLOOKUP returns #VALUE! when A2:A8 and C2:C7 do not have the same number of rows, and if_not_found does not catch it. Make both ranges cover the same rows.

Expecting "" to act like an empty cell

"" is text. Return 0 if the result is used in arithmetic, or test for it with =cell="" rather than ISBLANK.

Getting 0 for items that are found

The matching cell in the return column is empty. Use =IF(XLOOKUP(...)="","label",XLOOKUP(...)) to show your own label.

FAQ

How do I make XLOOKUP return blank instead of #N/A?

Put "" as the fourth argument: =XLOOKUP(value,lookup_range,return_range,""). The cell then looks empty when the value is missing. It holds empty text, so do not multiply it.

Why does XLOOKUP return 0 instead of blank?

The value was found, but the matching cell in the return range is empty, and Excel displays an empty result as 0. Test with =IF(XLOOKUP(...)="","",XLOOKUP(...)) to show a blank.

Is XLOOKUP's if_not_found the same as IFNA?

For a value that is missing, yes: both replace #N/A. if_not_found is shorter and does not catch an #N/A that is sitting in the return cell itself, while IFNA around XLOOKUP catches that too.

Can if_not_found run another lookup?

Yes. Any formula works in the fourth argument, so =XLOOKUP(x,list1,prices1,XLOOKUP(x,list2,prices2,"Not found")) searches a second table only when the first has no match.

Functions used

Related guides

Updated November 2, 2026