How to return a default value when a lookup finds nothing
✓ Verified in LibreOffice 25.8.7.3Stop lookups showing #N/A when the value isn't in the table — show "Not found" (or 0) instead.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =XLOOKUP(D2,A2:A4,B2:B4,"Not found") | XLOOKUP's 4th argument is the built-in not-found result. Older Excel: =IFERROR(VLOOKUP(D2,A2:B4,2,FALSE),"Not found"). |
| Google Sheets | =XLOOKUP(D2,A2:A4,B2:B4,"Not found") | Identical; the IFERROR+VLOOKUP form also works. |
| LibreOffice Calc | =XLOOKUP(D2,A2:A4,B2:B4,"Not found") | XLOOKUP needs LibreOffice 24.8+; earlier versions use IFERROR(VLOOKUP(...)). |
How it works
XLOOKUP takes the not-found result as its fourth argument — no error-wrapping needed. "pear" isn't in the fruit list, so the formula returns "Not found" instead of #N/A. The classic IFERROR(VLOOKUP(...)) pattern does the same in any version, but note IFERROR hides all errors (including typos in the range), while XLOOKUP's argument only handles the genuine not-found case.
Verified, not just documented
We ran =XLOOKUP(D2,A2:A4,B2:B4,"Not found") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned Not found — exactly the expected result. Every formula here is confirmed by actually executing it.