XLOOKUP misses without blowing up (IFERROR)
Customer service keeps typing SKUs that stopped existing, and every miss splatters #N/A across the price report. Look up the price for the SKU in E2 from the list in A2:C6, and return the text "Not found" instead of an error. Fill the Price cell F2 — two ways solve this, and one of them doesn't even need IFERROR.
Your solution should use cell references so it updates automatically when Test Case input values change.
Notes
Two idiomatic fixes. The classic: wrap the lookup — =IFERROR(XLOOKUP(…), "Not found"). The modern one: XLOOKUP has a built-in 4th argument, if_not_found — =XLOOKUP(E2, A2:A6, C2:C6, "Not found") handles misses natively with no wrapper. Prefer the 4th argument when your audience has modern Excel: it only traps misses, whereas IFERROR also swallows real breakage like #REF! from deleted columns.
Function guide
FAQ
How do I make XLOOKUP return blank instead of #N/A?
Use the 4th argument: =XLOOKUP(E2, A2:A6, C2:C6, "") returns an empty-looking cell on a miss. For a visible message use =XLOOKUP(E2, A2:A6, C2:C6, "Not found").
IFERROR or XLOOKUP's if_not_found — which should I use?
With modern Excel or Google Sheets, prefer the 4th argument: it traps only misses. IFERROR(x, y) also hides #REF!, #VALUE!, and #DIV/0! — convenient, but it can hide structural breakage a miss never would.
Does Google Sheets XLOOKUP support if_not_found?
Yes. Sheets' XLOOKUP has the same 4th argument, so =XLOOKUP(E2, A2:A6, C2:C6, "Not found") works identically. On very old Excel versions without XLOOKUP, use =IFERROR(VLOOKUP(…), "Not found") instead.