Practice free

IFNA function

IFNA returns a formula's result, or a value you choose if the result is the #N/A error. Because it catches only #N/A, other errors still show up and can be fixed.

Example

Read it as a sentence: look for Zither in the first column of B1:E7 and give back its 2nd column, but show missing if the lookup finds no match.

Practice this in the app

Syntax

IFNA(value, value_if_na)

value (required)
The formula or value to try.
value_if_na (required)
What to return if value is #N/A.

Returns: value, or value_if_na if value is #N/A.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=IFNA(NA(),"x") x N/A replaced
=IFNA(5,"x") 5 Value passes
=IFNA({1,#N/A},0) 1 Array spills
=IFNA(VLOOKUP("Zither",Items!B1:E7,2,FALSE),"missing") missing Lookup idiom
=IFNA(Data!F1,"x") 0 Blank value
=IFNA(1/0,"x") #DIV/0! Other error passes

The Items sheet these formulas read

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Other errors such as #REF! or #VALUE! pass through unchanged, which is usually the point.

Where it works

Excel
IFNA arrived in Excel 2013. Older versions do not have it.
Google Sheets
Same name, same behaviour for everyday use.

Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .