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.
Excel shows missing
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
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.
| Formula | Excel shows | Case |
|---|---|---|
=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
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
Data
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 .