Spreadsheet Practice

ISNA function

Quick answer

ISNA returns TRUE only for the #N/A error, the one MATCH, VLOOKUP and XLOOKUP return when a value isn't found.

Syntax
=ISNA(value)
Most common use
=IF(ISNA(MATCH(D2,A2:A5,0)),"Not in price list","Listed") returns Listed (B-210 is in the list)

Use it to turn a failed lookup into a clear "not listed" message while letting real errors, like a typo in a function name, still show.

Available in
Every current version
Category
Information functions
Returns
TRUE if the value is #N/A, FALSE otherwise.
Google Sheets
Available in Google Sheets with the same argument.

Syntax

=ISNA(value)

ArgumentRequired?What it is
valueRequiredThe value, cell or formula to test.

Examples

Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.

CellFormulaResultWhat it shows
E2=IF(ISNA(MATCH(D2,A2:A5,0)),"Not in price list","Listed")ListedB-210 is in the list
E3=IF(ISNA(MATCH(D3,A2:A5,0)),"Not in price list","Listed")Not in price listZ-999 isn't, so MATCH returns #N/A
F2=SUMPRODUCT(--ISNA(MATCH(D2:D4,A2:A5,0)))2Two of the three order SKUs are missing

Try it live

This is the sample sheet. Change any number or text, or edit a formula, and the results update.

Show the sample data as a table
SKUPriceOrder SKUCheckSKUs not in the list
A-100$39B-210Listed2
B-210$89Z-999Not in price list
C-330$54X-500
D-440$29

Practice ISNA

Graded instantly, then re-checked on changed data.

New or listed?

In E2, return New item if the SKU in D2 isn't in A2:A6, otherwise Listed. Use MATCH and ISNA, and fill down to E6.

Common mistakes

What you seeWhy, and how to fix it
Not found when the item is clearly thereLookups return #N/A for extra spaces or for numbers stored as text. Clean the data with TRIM or VALUE before blaming the formula.
The lookup runs twiceIF(ISNA(VLOOKUP(…)),"",VLOOKUP(…)) repeats the lookup. IFNA(VLOOKUP(…),"") does it once.
Other errors still showISNA ignores everything except #N/A, so a #REF! or #VALUE! passes through. That's usually what you want; fix the cause rather than hiding it.

Tips

  • Test whether a value exists in a list: =NOT(ISNA(MATCH(D2,A2:A5,0))).

Questions

What is the difference between ISNA and IFNA?

ISNA returns TRUE or FALSE. IFNA returns the value, or your fallback when the value is #N/A. Use IFNA to replace the error and ISNA to test for it.

How do I check if a value exists in a list?

=NOT(ISNA(MATCH(D2,A2:A5,0))) is TRUE when D2 is in the list. =COUNTIF(A2:A5,D2)>0 is another way.

Answers that use ISNA

In other languages

ISNA is ESNOD in Spanish Excel and ISTNV in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.

Updated Checked by running: every formula on this page was run by our formula engine on .