How to return a message instead of #N/A in a lookup
Formula (Excel and Google Sheets)
=XLOOKUP(G1,A2:A9,C2:C9,"Not found")Give XLOOKUP its if_not_found argument, =XLOOKUP(value, lookup, return, "Not found"), or wrap an older lookup in IFNA.
- Result on the example
- Not found in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(G1,A2:A9,C2:C9,"Not found")Result"Not found"
G1"P-120"The value in G1.
A2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …A range: 8 cells in one column.
C2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …A range: 8 cells in one column.
XLOOKUP(G1,A2:A9,C2:C9,"Not found")"Not found"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
G1"P-120" - lookup_array
A2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", … - return_array
C2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, … - if_not_found
"Not found"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(G1,A2:A9,C2:C9,"Not found"). We ran it in Google Sheets mode on the same data and it returns Not found.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IFNA(VLOOKUP(G1,A2:D9,3,FALSE),"Not found") | 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") | 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).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Return a message instead of #N/A in a lookup
G2, look up the price of the SKU in G1, and show "Not found" when it isn't in the list.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.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.