Returns a value unchanged if it is not an error and a different value otherwise. IFERROR (Value1 / 0, 10) IFERROR (Value1 / 0; 10) always returns 10 (because dividing a number by zero results in an error).

There are different types of errors. To learn the exact error type, use ERROR.TYPE. To determine if a value is an error, use ISERROR.

#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.

IFERROR and arrays

Applying IFERROR to an array yields an array with the same size as the original array, where 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 error, IFERROR returns an array with a single element. This formula returns the array { 99 }{ 99 }, as the GROWTH function returns an error on account of the two arrays not being equal in size:

An empty array contains no errors. FILTER returns an empty array when no values match, so IFERROR cannot be used to detect that case. Give FILTER a third parameter instead, which it returns when no values match. This formula returns {  - 50 }{  - 50 }: