IFERROR function

Returns a value unchanged if it is not an error and a different value otherwise. IFERROR(Field1 / 0, 10)IFERROR(Field1 / 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.

Errors are never displayed to your users

When you preview your app, any errors are shown in red. This is meant to help you, the author of the app, determine the cause of potentially unexpected behavior. Errors are also shown when you sign into Calcapp Connect with your credentials and run your own apps.

However, these errors are not shown in shared apps, meaning that errors are never shown to your users. Instead, a field with an error looks like a blank field in shared apps.

If you want to hide errors from showing up in the preview and in Calcapp Connect, use a formula like the following:

IFERROR([your formula], BLANK())IFERROR([your formula]; BLANK())

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

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.

Unlike IFNA and IFERR, 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:

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

If the value of Field2 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

Invoking IFERROR with an array as its first parameter applies it to the array as a whole, not to its constituent elements.

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:

IFERROR(GROWTH({ 1 }, { 1, 2 }), { 99 })IFERROR(GROWTH({ 1 }; { 1; 2 }); { 99 })

Use MAP to invoke IFERROR once for every array element instead. This formula returns the array { 1, 2, -1, 4 }{ 1; 2; -1; 4 }:

MAP({ 1, 2, NA(), 4 }, IFERROR(Element, -1))MAP({ 1; 2; NA(); 4 }; IFERROR(Element; -1))

Above, IFERROR is invoked once for each array element. When it encounters the third array element, which is an #N/A error, it returns -1 instead of the array element. MAP incorporates all return values from IFERROR into the result array.

Examples

IFERROR(Field1 / Field2, 0)IFERROR(Field1 / Field2; 0)

Returns the result of dividing Field1.ValueField1,Value by Field2.ValueField2,Value if there is no error, and 0 otherwise.

IFERROR(Field1 / Field2, BLANK())IFERROR(Field1 / Field2; BLANK())

Returns the result of dividing Field1.ValueField1,Value by Field2.ValueField2,Value if there is no error, and a blank value otherwise.

IFERROR(XLOOKUP(10, { 20, 30 }, { 200, 300 }), 500)IFERROR(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. IFERROR catches all errors, though, not just the #N/A error returned from XLOOKUP when it cannot find a value.

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 catches only #N/A errors, unlike IFERROR, which catches all errors. If the intent is to only return 500 if the value cannot be found, it is better to use IFNA.

IFERROR(GROWTH({ 1 }, { 1, 2 }), { 99 })IFERROR(GROWTH({ 1 }; { 1; 2 }); { 99 })

Returns the array { 99 }{ 99 }, as the GROWTH function returns an error on account of the two arrays not being equal in size.

MAP({ 1, 2, NA(), 4 }, IFERROR(Element, -1))MAP({ 1; 2; NA(); 4 }; IFERROR(Element; -1))

Returns the array { 1, 2, -1, 4 }{ 1; 2; -1; 4 }. IFERROR is invoked once for each array element and returns all array elements unchanged, except for the third array element. That element is an #N/A error, and causes IFERROR to return -1. MAP incorporates all return values from IFERROR into the result array.