ISNA Function (LibreOffice Calc)
The ISNA function in LibreOffice Calc checks whether a value results in the #N/A error. It is essential for lookup validation, error handling, and distinguishing 'not found' from other error types.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the ISNA Function Does ▾
- Returns TRUE only for the
#N/Aerror - Returns FALSE for all other errors
- Useful for lookup functions that return
#N/Awhen no match is found - Works with literals, formulas, and references
It is designed to be precise, selective, and essential for lookup workflows.
Syntax ▾
ISNA(value)
Arguments
- value:
Any value, expression, or cell reference.
Basic Examples ▾
Check if A1 contains #N/A
=ISNA(A1)
Check a literal #N/A
=ISNA(#N/A)
Returns TRUE.
Check a VLOOKUP result
=ISNA(VLOOKUP(A1; B1:C10; 2; 0))
TRUE if A1 is not found.
Check a non-#N/A error
=ISNA(#REF!)
Returns FALSE.
Advanced Examples ▾
Wrap a VLOOKUP to handle missing values
=IF(ISNA(VLOOKUP(A1; B1:C10; 2; 0)); "Not found"; VLOOKUP(A1; B1:C10; 2; 0))
Use IFNA for cleaner syntax
=IFNA(VLOOKUP(A1; B1:C10; 2; 0); "Not found")
Equivalent to the ISNA pattern.
Detect missing matches in MATCH
=ISNA(MATCH(A1; B1:B10; 0))
TRUE if A1 is not in the list.
Validate lookup chains
=IF(ISNA(MATCH(A1; B1:B10; 0)); "Missing"; "OK")
Distinguish #N/A from other errors
=IF(ISNA(A1); "Not found"; IF(ISERR(A1); "Error"; "OK"))
Detect missing data in imported datasets
=ISNA(A1)
Useful when external sources use #N/A placeholders.
Check for #N/A before performing calculations
=IF(ISNA(A1); ""; A1 * 2)
Edge Cases and Behavior Details ▾
ISNA catches ONLY #N/A
-
TRUE for:
- #N/A
-
FALSE for:
- #REF!
- #VALUE!
- #DIV/0!
- #NAME?
- #NUM!
- #NULL!
- text
- numbers
- blanks
ISNA vs ISERR vs ISERROR
- ISNA → catches only #N/A
- ISERR → catches all errors except #N/A
- ISERROR → catches all errors
ISNA on a blank cell
=ISNA(A1)
Returns FALSE.
ISNA on a formula returning ""
=ISNA("")
Returns FALSE.
ISNA on text
=ISNA("text")
Returns FALSE.
Common Errors and Fixes ▾
ISNA returns TRUE unexpectedly
Cause:
- Lookup returned #N/A
- MATCH did not find a value
- Reference points to a #N/A cell
Fix:
Handle with IFNA or provide fallback values.
ISNA returns FALSE unexpectedly
Cause:
- Error is not #N/A
- Value is text
- Value is blank
- Value is numeric
ISNA used on a range
=ISNA(A1:A10)
Returns a single TRUE/FALSE in array context; use SUMPRODUCT to scan ranges.
Best Practices ▾
- Use ISNA to detect “not found” lookup results
- Use IFNA for cleaner fallback logic
- Use ISERR or ISERROR for broader error detection
- Validate lookup inputs before performing searches
- Use ISNA with MATCH and VLOOKUP for robust lookup workflows
Related Patterns and Alternatives ▾
- Use IFNA to handle #N/A directly
- Use ISERR to detect all errors except #N/A
- Use ISERROR to detect all errors
- Use TYPE to inspect value types
- Use IF to build custom error logic
By mastering ISNA and its companion functions, you can build precise, lookup‑aware spreadsheets in LibreOffice Calc.