Purpose
Return value
Syntax
=ISNA(value)- value - The value to check if #N/A.
Using the ISNA function
The ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. The ISNA function takes one argument, value, which is typically a cell reference.
Examples
If A1 contains the #N/A error, ISNA returns TRUE:
=ISNA(A1) // returns TRUE
ISNA returns FALSE for other values and errors:
=ISNA(100) // returns FALSE
=ISNA(5/0) // returns FALSE
You can use the ISNA function with the IF function test for #N/A and display a friendly message if the error occurs. For example, to display a message if A1 contains #N/A and the value of A1 if not:
=IF(ISNA(A1),"message",A1)
The IFNA function is a more efficient way to trap the #N/A error. See VLOOKUP without NA error for an example.
Return #N/A
To explicitly return the #N/A error in a formula, you can use the NA function:
=NA() // returns #N/A error
The following will return true:
=ISNA(NA()) // returns TRUE
Count #N/A errors
To count cells in a range that contain #N/A errors, you can use the SUMPRODUCT function like this:
=SUMPRODUCT(--ISNA(range))
The double negative coerces the TRUE and FALSE results from ISNA into 1s and 0s and SUMPRODUCT sums the result.
Notes
- The IFNA function is a more efficient way to trap and handle the #N/A error.
ISNA function examples
Range contains a value not in another range
VLOOKUP without #N/A error
Count missing values
Count cells not equal to many things
Count not equal to multiple criteria
Related functions
ISERROR Function
The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or run a different calculation when an error occurs....
IFERROR Function
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
ISBLANK Function
The Excel ISBLANK function returns TRUE when a cell is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE.
ISERR Function
The Excel ISERR function returns TRUE for any error type except the #N/A error. You can use the ISERR function together with the IF function to test for an error and display a custom message, or perform a different calculation if found.
ISEVEN Function
The Excel ISEVEN function returns TRUE when a value is an even number, and FALSE when a value is an odd number. ISEVEN will return the #VALUE error if a value is not numeric.
ISFORMULA Function
The Excel ISFORMULA function returns TRUE if a cell contains a formula, and FALSE if not. When a cell contains a formula ISFORMULA will return TRUE regardless of the formula's output or error conditions.
ISLOGICAL Function
The Excel ISLOGICAL function returns TRUE when a cell contains the logical values TRUE or FALSE, and returns FALSE for cells that contain any other value, including empty cells.
ISNONTEXT Function
The Excel ISNONTEXT function returns TRUE when a cell contains any value except text. This includes numbers, dates, times, errors, and formulas that do not return text. ISNONTEXT also returns TRUE when a cell is empty.
ISNUMBER Function
The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.
ISODD Function
The Excel ISODD function returns TRUE when a value is an odd number, and FALSE when a value is an even number. ISODD will return the #VALUE error if a value is not numeric.
ISREF Function
The Excel ISREF returns TRUE when a cell contains a reference and FALSE if not. You can use the ISREF function to check for a reference in a formula.
ISTEXT Function
The Excel ISTEXT function returns TRUE when a cell contains a text value, and FALSE if the cell contains any other value. You can use the ISTEXT function to check if a cell contains a text value, or a numeric value entered as text.
