Learn Blog Pricing About Sign in

Learn

Blog

Pricing

About

Sign in

NA function

NA() NA()

Returns

A "not available" error.

Returns an #N/A error ("not available"). As the ISNA function returns whether its parameter is 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 }))

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 preceding formula returns TRUE if the value of Field2 is 0 or blank, resulting in a #DIV/0! error, but it returns FALSE if the value resulting from the Field1 / Field2Field1 / Field2 calculation cannot be found in the lookup array.

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.