What it does
#N/A means "value not available" and mostly comes from lookup functions such as VLOOKUP, MATCH and HLOOKUP. IFNA catches only this error: when the value is not found it shows "No price", but if the formula divides by zero or hits bad data, #DIV/0! or #VALUE! still shows.
The distinction matters. IFERROR covers every error with the same message, so a real problem, such as a quantity typed as a word, also shows as "No price" and goes unnoticed. With IFNA, only the expected case is hidden.
It is available in Excel 2013 and later. In Microsoft 365 and Excel 2021, XLOOKUP takes its not-found value as the fourth argument, so IFNA is not needed there.
Syntax
=IFNA(value, value_if_na)=EĞERYOKSA(değer; yoksa)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
value | Required | The formula whose result you want, usually a lookup: VLOOKUP(A2,$E$2:$F$5,2,FALSE). |
value_if_na | Required | The value returned if value is #N/A: "No price", 0 or "". |
Examples
1Flag a code that is missing from the price list
The codes are looked up in the price list in E:F. For a missing code VLOOKUP returns #N/A; IFNA shows "No price" in that case only.
=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),"No price")| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product Code | Unit Price | Code | Price | ||
| 2 | KR-110 | 42.50 | KR-110 | 42.50 | ||
| 3 | KR-135 | No price | KR-120 | 165.00 | ||
| 4 | KR-120 | 165.00 | KR-130 | 38.90 | ||
| 5 | KR-150 | 19.75 | KR-150 | 19.75 |
- B2 (filled down to B5)
=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),"No price")
KR-135 is not in the price list, so B3 shows No price; the other codes return their price, 42.50 for KR-110.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Do not hide a real data error
The amount is price times quantity. KR-135 has no price, and KR-120's quantity was typed as a word by mistake. IFNA keeps these two cases apart.
=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE)*B2,"No price")| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product Code | Qty | Amount | Code | Price | |
| 2 | KR-110 | 12 | 510.00 | KR-110 | 42.50 | |
| 3 | KR-135 | 5 | No price | KR-120 | 165.00 | |
| 4 | KR-120 | twelve | #VALUE! | KR-130 | 38.90 | |
| 5 | KR-130 | 20 | 778.00 | KR-150 | 19.75 |
- C2 (filled down to C5)
=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE)*B2,"No price")
KR-135 has no price, so C3 shows No price. C4 shows #VALUE!: this is not a not-found case but a data error to fix, and IFNA does not hide it. Written with IFERROR, C4 would also say "No price".
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: The formula has an error other than #N/A, for example a quantity typed as a word was multiplied; IFNA deliberately keeps showing it.
Fix: Fix the source data; if you want to hide this error too, use IFERROR.
- #NAME?
Cause: The file was opened in a version without IFNA (Excel 2010 or earlier).
Fix: In those versions use the pattern
IF(ISNA(…),…,…)(ISNA).
Good to know
- Return 0 for missing rows so they do not break a total:
=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),0). - Put the whole lookup inside IFNA; a multiplication or division left outside does not create #N/A by itself, but it can create other errors.
In Nexell
Get Nexell to do it from a sentence
Add the unit price from the price list to the order list and calculate the amount; write "No price" where the price is missing
Convert to tableBring the order list and the price list into the tool and type this. Nexell builds the match and the amount with real Excel formulas, and the preview shows the rows with no price; in the downloaded file, the amounts change when the price list is updated.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between IFNA and IFERROR?
IFNA catches only #N/A; IFERROR catches every error, including #DIV/0!, #VALUE! and #REF!. In lookup formulas, IFNA hides the not-found case while leaving real errors in the formula visible.
Related functions
- IFERROREĞERHATA · Returns a fallback if the value is an error.
- VLOOKUPDÜŞEYARA · Looks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.
- XLOOKUPÇAPRAZARA · Searches an array and returns the matching item; the fourth argument if not found.
- MATCHKAÇINCI · Position of a value in a list (0 = exact match).
- ISNAEYOKSA · TRUE if the value is #N/A.