ISNA function

Returns whether the given value is an #N/A error ("not available"). As the NA function returns an #N/A error, ISNA(NA())ISNA(NA()) returns TRUE.

Lookup functions like XLOOKUP return #N/A errors when a value cannot be found. The ISNA function may be used to detect the #N/A error specifically.

This formula returns TRUE, because the sought value, 10, is not part of the { 20, 30 }{ 20; 30 } lookup array given to XLOOKUP:

ISNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }))ISNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }))

The IFNA function is used to return a specific value if a value is an #N/A error, or the value itself otherwise. This formula returns 500, because the sought value, 10, is not part of the { 20, 30 }{ 20; 30 } lookup array given to XLOOKUP:

IFNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }); 500)

ISERR is the opposite of ISNA, meaning that it detects all errors except for #N/A errors. Consider this formula:

ISERR(XLOOKUP(Field1 / Field2, { 20, 30 }, { 200, 300 }))ISERR(XLOOKUP(Field1 / Field2; { 20; 30 }; { 200; 300 }))

The formula above returns TRUE if the value of Field2 is 0 or blank, resulting in a #DIV/0! error ("division by zero"), but it returns FALSE if the value resulting from the Field1 / Field2Field1 / Field2 cannot be found in the lookup array.

ISNA and arrays

Applying ISNA to an array only yields a single value, related to the array value itself and not its constituent elements.

This formula returns FALSE, as the array as a whole is not an error, even if its first element is an #N/A error and its second element is a #DIV/0! error:

ISNA({ NA(), 4 / Field2 })ISNA({ NA(); 4 / Field2 })

(The formula above requires that the value of Field2 is 0 or blank.)

Use MAP to invoke ISNA once for every array element and have the results returned as a logical array.

This formula returns { TRUE, FALSE }{ TRUE; FALSE }, indicating that the first array element is an #N/A error and the second array element is not (it is a #DIV/0! error):

MAP({ NA(), 4 / Field2 }, ISNA(Element))MAP({ NA(); 4 / Field2 }; ISNA(Element))

Examples

ISNA(NA())ISNA(NA())

Returns TRUE, as the NA function returns an #N/A error.

ISNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }))ISNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }))

Returns TRUE, because the sought value, 10, is not part of the { 20, 30 }{ 20; 30 } lookup array given to XLOOKUP.

IFNA(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFNA(XLOOKUP(10; { 20; 30 }; { 200; 300 }); 500)

Returns 500, because the sought value, 10, is not part of the { 20, 30 }{ 20; 30 } lookup array given to XLOOKUP. The IFNA function is used to return a specific value if a value is an #N/A error, or the value itself otherwise.

ISNA({ 4, NA() })ISNA({ 4; NA() })

Returns FALSE, as the array as a whole is not an error.

MAP({ 4 / Field2, NA() }, ISNA(Element))MAP({ 4 / Field2; NA() }; ISNA(Element))

Returns { FALSE, TRUE }{ FALSE; TRUE }, indicating that the first array element is not an #N/A error (it is a #DIV/0! error) and the second array element is an #N/A error. (The value of Field2 must be 0 or blank in order for the first array element to be a #DIV/0! error.)