Skip to content

Excel function · Logical

Excel IFNA function

IFNA shows a value of your choice when a formula returns #N/A and leaves every other error as it is. Use it in lookup formulas to tell "not found" apart from real mistakes.

In Turkish Excel: EĞERYOKSA

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

English Excel=IFNA(value, value_if_na)
Turkish Excel=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

ArgumentRequiredDescription
valueRequiredThe formula whose result you want, usually a lookup: VLOOKUP(A2,$E$2:$F$5,2,FALSE).
value_if_naRequiredThe 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.

B2fx=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),"No price")
Flag a code that is missing from the price list: example table
ABCDEF
1Product CodeUnit PriceCodePrice
2KR-11042.50KR-11042.50
3KR-135No priceKR-120165.00
4KR-120165.00KR-13038.90
5KR-15019.75KR-15019.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.

C2fx=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE)*B2,"No price")
Do not hide a real data error: example table
ABCDEF
1Product CodeQtyAmountCodePrice
2KR-11012510.00KR-11042.50
3KR-1355No priceKR-120165.00
4KR-120twelve#VALUE!KR-13038.90
5KR-13020778.00KR-15019.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

Type this in Nexell

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 table

Bring 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

All logical functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free