# How to return a message instead of #N/A in a lookup

Give XLOOKUP its if_not_found argument, =XLOOKUP(value, lookup, return, "Not found"), or wrap an older lookup in IFNA.

**Excel:** `=XLOOKUP(G1,A2:A9,C2:C9,"Not found")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Price | Stock |  | SKU | P-120 |
| P-101 | Desk lamp | 39.99 | 140 |  | Price |  |
| P-102 | Office chair | 249 | 32 |  |  |  |
| P-103 | Monitor arm | 89.5 | 75 |  |  |  |
| P-104 | Standing desk | 449 | 18 |  |  |  |
| P-105 | Keyboard | 74.95 | 210 |  |  |  |
| P-106 | Webcam | 59 | 96 |  |  |  |
| P-107 | Headset | 129 | 54 |  |  |  |
| P-108 | Laptop stand | 45 | 160 |  |  |  |

Formula in G2: `=XLOOKUP(G1,A2:A9,C2:C9,"Not found")`

Result: Not found

## How it works

XLOOKUP's fourth argument is what to return when there is no match. Without it you get #N/A, which also breaks any total that refers to the cell.

## Other ways

- `=IFNA(VLOOKUP(G1,A2:D9,3,FALSE),"Not found")`: For VLOOKUP and INDEX/MATCH. IFNA catches only #N/A, so a real mistake (like #REF!) still shows.
- `=IFERROR(VLOOKUP(G1,A2:D9,3,FALSE),"Not found")`: Catches every error. Convenient, but it hides broken formulas too.

## Common mistakes

- Returning "" instead of a message makes missing items look like blanks, which is easy to miss.
- Use 0 instead of text when the result feeds a calculation: `=XLOOKUP(G1,A2:A9,C2:C9,0)`.

## Questions

**What is the difference between IFNA and IFERROR?**

IFNA replaces only #N/A (not found). IFERROR replaces every error, including ones caused by mistakes in the formula.

**Why do I still get #N/A for a value I can see?**

The lookup value and the list differ: a trailing space, or a number stored as text in one of them.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/lookup-return-text-if-not-found