ISNA function
ISNA returns TRUE only for the #N/A error, the one MATCH, VLOOKUP and XLOOKUP return when a value isn't found. 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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.