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)
| Argument | Required? | What it is |
|---|---|---|
value | Required | The 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.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =IF(ISNA(MATCH(D2,A2:A5,0)),"Not in price list","Listed") | Listed | B-210 is in the list |
| E3 | =IF(ISNA(MATCH(D3,A2:A5,0)),"Not in price list","Listed") | Not in price list | Z-999 isn't, so MATCH returns #N/A |
| F2 | =SUMPRODUCT(--ISNA(MATCH(D2:D4,A2:A5,0))) | 2 | Two 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
| SKU | Price | Order SKU | Check | SKUs not in the list | |
| A-100 | $39 | B-210 | Listed | 2 | |
| B-210 | $89 | Z-999 | Not in price list | ||
| C-330 | $54 | X-500 | |||
| D-440 | $29 |
Practice ISNA
Graded instantly, then re-checked on changed data.
New or listed?
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 see | Why, and how to fix it |
|---|---|
| Not found when the item is clearly there | Lookups 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 twice | IF(ISNA(VLOOKUP(…)),"",VLOOKUP(…)) repeats the lookup. IFNA(VLOOKUP(…),"") does it once. |
| Other errors still show | ISNA 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 .