ISNA in Google Sheets returns TRUE only for the actual #N/A error. Use it to flag unsuccessful lookups, test membership in a list, or choose a fallback with IF.
Check Cells for the N/A Error
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Value |
|---|
| =NA() |
| 42 |
| Ready |
| =1/0 |
| #N/A |
Enter this formula in E2:
=ISNA(A2)E2 returns TRUE because A2 contains the formula =NA(). The number, ordinary text, division error, and literal text “#N/A” do not satisfy ISNA.
For the last row, enter '#N/A with a leading apostrophe to keep it as text. The apostrophe is an entry marker, not part of the displayed value.
ISNA Function Syntax
=ISNA(value)The value can be a cell, a formula, or a literal. ISNA returns a Boolean result, not a replacement value. Other errors return FALSE, which does not mean the original formula is correct.
Use IFERROR to replace any error, or IFNA to replace only #N/A. ISNA is useful when you need the TRUE/FALSE test itself.
Check a Range or Count N/A Errors
=ARRAYFORMULA(ISNA(A2:A6))The results are TRUE, FALSE, FALSE, FALSE, FALSE. Enter the formula in an empty column with five available cells. To count the actual #N/A errors, use:
=SUM(ARRAYFORMULA(N(ISNA(A2:A6))))This returns 1. N converts TRUE to 1 and FALSE to 0, so SUM counts the errors that ISNA identifies.
Find Names Missing from a Roster with MATCH
Enter this dataset starting in A12. Leave cells marked “(blank)” empty.
| Roster |
|---|
| Maya |
| Noah |
| Ari |
=ISNA(MATCH("Lena",A13:A15,0))This returns TRUE, because Lena is absent. Replacing Lena with Maya returns FALSE. MATCH uses 0 for an exact match; ISNA tests whether that lookup failed with #N/A.
Replace N/A with a Friendly Message
Enter this dataset starting in A22. Leave cells marked “(blank)” empty.
| ID | Customer |
|---|---|
| C001 | Maya |
| C002 | Noah |
| C003 | Ari |
=IF(ISNA(VLOOKUP("C999",A23:B25,2,FALSE)),"Not Found",VLOOKUP("C999",A23:B25,2,FALSE))The missing ID C999 returns Not Found. Using C001 returns Maya. IF chooses the fallback when ISNA is TRUE, otherwise it returns the lookup value.
When you only need this replacement, IFNA avoids writing the lookup twice:
=IFNA(VLOOKUP("C999",A23:B25,2,FALSE),"Not Found")This returns the same Not Found message. It leaves other errors visible, which helps distinguish a missing lookup result from a broken range or column index.
Check a VLOOKUP Directly
=ISNA(VLOOKUP("C999",A23:B25,2,FALSE))The result is TRUE. This pattern skips a helper column when you only need a flag. Use it for customer IDs, city names, or other keys in a reference table.
Check the lookup itself. A mismatched text/number key can also produce #N/A, and a matching row may contain #N/A in its return cell. TRUE alone does not prove a record is absent.
Other Google Sheets articles you may also like