ISNA function
ISNA returns TRUE only for the #N/A error, which failed lookups return. IFNA does the same check and gives a fallback value in one step.
Example
Read it as a sentence: look for Zither in B2:B7 with an exact match, then check whether that lookup came back as #N/A, which gives TRUE.
Excel shows TRUE
B
1
Item
2
Boxer cartridge
3
Briar pipe
4
Candle
5
Cigar
6
Clay pipe
7
Commonplace book
Items
Syntax
ISNA(value)
-
value(required) - The value to check.
Returns: TRUE only for #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 |
|---|---|---|
=ISNA(NA()) | TRUE | N/A |
=ISNA(1/0) | FALSE | Other error |
=ISNA(5) | FALSE | Value |
=ISNA(MATCH("Zither",Items!B2:B7,0)) | TRUE | Failed match |
=ISNA(Data!F11) | TRUE | N/A cell |
The Items sheet these formulas read
B
1
Item
2
Boxer cartridge
3
Briar pipe
4
Candle
5
Cigar
6
Clay pipe
7
Commonplace book
Items
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
4
TRUE
5
=1/0
6
Hello World
7
0
8
=""
9
abc
10
12.5
11
=NA()
Data
Watch out for
- IFNA does this and gives a fallback in one step.
Where it works
- Excel
- ISNA is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Information functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .