Returns a value unchanged if it is not an #N/A (not available) error and a different value otherwise. IFNA ( NA (), 4) IFNA ( NA (); 4) returns 4, because NA returns an #N/A error.

#N/A errors

Lookup functions like XLOOKUP return #N/A errors ("not available") 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:

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:

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

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

Unlike IFNA, IFERROR detects all errors. If the intent is to detect #N/A errors specifically, using IFERROR is a mistake.

For an example of why using IFERROR instead of IFNA can be a mistake, consider this formula:

If Value2 is zero, or is blank, this will cause a #DIV/0! ("division by zero") error to be returned from the / operator, which XLOOKUP will pass on to IFERROR, which in turn will cause IFERROR to return 500 instead of the #DIV/0! error.

If the intent is to only return 500 if the sought value cannot be found in the lookup array, it is better to use the formula IFNA ( XLOOKUP (10, { 20, 30 }, { 200, 300 }), 500) IFNA ( XLOOKUP (10; { 20; 30 }; { 200; 300 }); 500) and let the #DIV/0! error slip past IFNA.

IFNA and arrays

Applying IFNA to an array yields an array with the same size as the original array, where #N/A errors are replaced with the second parameter and all other elements are returned unchanged.

This formula returns the array { 1, 2, - 1, 4 }{ 1; 2; - 1; 4 }, as the third array element is an #N/A error:

If the array is itself an #N/A error, IFNA returns an array with a single element. This formula returns the array { 99 }{ 99 }, as the XLOOKUP function returns an #N/A error as the value 3 cannot be found in the { 1, 2 }{ 1; 2 } lookup array:

(The XLOOKUP variant used above takes a formula fragment as its first parameter. Only that variant supports returning arrays.)