Excel ISNA Function: Syntax, Examples and Tips

Q: Can ISNA be used on an array?

Yes. SUMPRODUCT(--ISNA(range)) counts the #N/A cells in any version, and Excel 365 spills ISNA(range) as an array of TRUE/FALSE values.

Part of the free Module 5: Excel Formulas and Functions · Function 47 of 105 · Full Excel course

The Excel ISNA function returns TRUE when a value is the #N/A error and FALSE for anything else, including other error types. ISNA in Excel is the precise test for “lookup value not found”: wrap it around VLOOKUP, MATCH or INDEX-MATCH to report missing items without hiding genuine formula faults such as #REF! or #VALUE!.

Table of Contents

ISNA syntax

=ISNA(value)

Arguments

Argument	Required	Meaning
value	Required	The cell, expression or formula to test for #N/A.

ISNA works in every version of Excel. Since Excel 2013 the IFNA function does the test-and-replace in one step, but ISNA remains useful for counting, conditional formatting and files shared with older versions.

Step-by-step example

Cell A1 contains =VLOOKUP(“Zebra”, D:E, 2, FALSE) and the table has no Zebra, so it shows #N/A.

=ISNA(A1)

Excel evaluates A1 and finds #N/A.
#N/A is exactly the error ISNA looks for.
The result is TRUE. If A1 showed #DIV/0! the result would be FALSE, and with a normal value it would also be FALSE.

Practical use cases

1. Friendly message for missing lookups (all versions)

=IF(ISNA(VLOOKUP(G2, $A$2:$B$500, 2, FALSE)), "Not found", VLOOKUP(G2, $A$2:$B$500, 2, FALSE))

In Excel 2013 and later, IFNA(VLOOKUP(…), “Not found”) is shorter and evaluates the lookup once.

2. Count how many items were not found

=SUMPRODUCT(--ISNA(E2:E500))

3. Compare two lists: items in A missing from D

=IF(ISNA(MATCH(A2, $D$2:$D$500, 0)), "Missing", "Present")

4. Highlight #N/A cells only

=ISNA(A2)

As a conditional-formatting rule this colours unmatched rows while leaving other errors in their default style.

5. Chart-friendly gaps

=IF(ISNA(B2), NA(), B2)

Line charts skip #N/A points instead of plotting zero, so deliberately keeping #N/A can be useful.

ISNA vs ISERR vs ISERROR

Function	#N/A	Other errors	Normal value
ISNA	TRUE	FALSE	FALSE
ISERR	FALSE	TRUE	FALSE
ISERROR	TRUE	TRUE	FALSE

Common mistakes and errors

Expecting ISNA to catch #REF! or #VALUE! – it does not. Those indicate a broken formula and should be fixed, not hidden; use ISERROR only if you truly want everything trapped.
Double evaluation – IF(ISNA(lookup), alt, lookup) runs the lookup twice. Prefer IFNA in Excel 2013+.
Text “#N/A” – typed text is not an error; ISNA returns FALSE. Use the NA() function to create a real #N/A.
Legacy array limits – ISNA(range) in one cell tests a single cell unless wrapped in SUMPRODUCT.

Tips and best practices

Use ISNA for detection and IFNA for replacement; both target only the #N/A error.
Compare lists with MATCH inside ISNA; it is faster than VLOOKUP because it reads one column.
Keep #N/A in chart source columns deliberately so line charts show gaps rather than zeros.
Count unmatched rows with SUMPRODUCT(–ISNA(range)) as a reconciliation KPI.
Do not use ISERROR around lookups unless you truly want to hide broken references as well.

Related functions

IFNA – test and replace #N/A in one step.
ISERROR and ISERR – broader error tests.
NA – generates a deliberate #N/A.
VLOOKUP and MATCH – the functions that produce #N/A when nothing matches.
Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.

Frequently asked questions

How do I ignore #N/A errors with ISNA?

Wrap the formula: =IF(ISNA(formula), “”, formula). In Excel 2013 and later, =IFNA(formula, “”) does the same with one evaluation.

What is the difference between ISNA and ISERROR?

ISNA returns TRUE only for #N/A. ISERROR returns TRUE for every error type. ISNA is safer around lookups because other errors stay visible and get fixed.