Cleanup function

Excel ISNA function

Q: How do I check if a value is not in a list in Excel?

Use =ISNA(MATCH(A2,list,0)). It returns TRUE when the value is missing. =COUNTIF(list,A2)=0 is another way.

ISNA returns TRUE when a value is the #N/A error and FALSE for anything else, including other errors. #N/A is what MATCH, VLOOKUP and XLOOKUP return when they cannot find something, so ISNA around a lookup is a clean "is it missing?" test.

=ISNA(value)

Arguments

value: The value or formula to test, usually a lookup such as MATCH(A2,list,0).

ISNA examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	D	E
1	Invoice SKU	Qty	Catalog SKU	Price
2	LMP-01	4	LMP-01	39
3	ARM-22	2	ARM-22	89
4	TRY-09	6	LMP-04	54
5	LMP-04	1	FTR-07	45
6	CSH-11	3

Download this sheet as an Excel practice file (.xlsx)

=ISNA(MATCH(A4,$D$2:$D$5,0))
Result: TRUE
TRY-09 is not in the catalog, so MATCH returns #N/A and ISNA returns TRUE. Fill it down to flag every missing SKU.
=IF(ISNA(MATCH(A2,$D$2:$D$5,0)),"Not in catalog","OK")
Result: OK
A readable status for each invoice line. LMP-01 is in the catalog, so this one says OK.
=SUMPRODUCT(--ISNA(MATCH(A2:A6,D2:D5,0)))
Result: 2
Counts the invoice SKUs missing from the catalog in one cell: TRY-09 and CSH-11, so 2.
=ISNA(1/0)
Result: FALSE
FALSE: dividing by zero is #DIV/0!, not #N/A. That is the point of ISNA. It only reacts to "not found", so real mistakes still show.

How to use ISNA

Write the lookup first and check it returns #N/A for a missing value.
Wrap it: =ISNA(your lookup).
Put that inside IF to show your own message, or inside SUMPRODUCT to count.
In Excel 2013 and later, IFNA(lookup,"message") is a shorter way to replace #N/A.

Common ISNA mistakes

Using ISERROR when you mean "not found"

ISERROR is also TRUE for #REF!, #VALUE! and #DIV/0!, so a broken formula looks like a missing item. ISNA hides only #N/A.

Writing the lookup twice

IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...)) runs the lookup twice and is easy to break when editing. IFNA(VLOOKUP(...),"") does the same once.

False #N/A from extra spaces

A trailing space or a number stored as text makes MATCH miss, so ISNA says the item is missing when it is not. Clean the data with TRIM, or check with =A2=D2.

ISNA FAQ

What is the difference between ISNA and IFNA?

ISNA returns TRUE or FALSE. IFNA returns the value, or your replacement when the value is #N/A. Use IFNA to replace the error and ISNA to test, flag or count.

How do I check if a value is not in a list in Excel?

Use =ISNA(MATCH(A2,list,0)). It returns TRUE when the value is missing. =COUNTIF(list,A2)=0 is another way.

What is the difference between ISNA and ISERROR?

ISNA is TRUE only for #N/A. ISERROR is TRUE for every error type, which can hide real problems.

Guides that use ISNA

Related functions

Updated November 5, 2026