Returns the error type of the given value. ERROR.TYPE (Value1 / Value2) ERROR.TYPE (Value1 / Value2) returns 2, indicating a #DIV/0! ("division by zero") error, if Value2 is zero or blank.

These are the values returned by this function:

Error codes returned by ERROR.TYPE

Error name	Return value
#CYCLE! — circular reference detected	-2
#BLANK! — invalid blank value	-1
#DIV/0! — division by zero	2
#VALUE! — invalid value	3
#REF! — invalid reference	4
#NUM! — invalid number	6
#N/A — not available	7
#CALC! — calculation error	14

Detecting all errors versus only #N/A errors

There is generally little need to determine the exact type of an error, except for the #N/A error ("not available"). Lookup functions like XLOOKUP return #N/A errors when a value cannot be found.

There is a specialized function for detecting #N/A errors: ISNA. ISNA is simpler to use than ERROR.TYPE, as it returns TRUE if the error is an #N/A error and FALSE otherwise.

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:

IFERROR versus IFNA

IFERROR is a more generic version of IFNA, because it detects all errors, and not just #N/A errors. This formula also returns 500:

However, it is probably a mistake to use IFERROR here instead of IFNA, because it catches all errors, rather than only #N/A errors.

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, catching all errors is a mistake. 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.

To detect if an error is any error except for an #N/A error, use the ISERR function.

ERROR.TYPE and arrays

Applying ERROR.TYPE to an array yields a number array with the same size as the original array, where each element is the error type of the corresponding element in the original array. Elements which are not errors yield #N/A errors.

If Value2 is zero or is blank, this formula returns { 2, 7 }{ 2; 7 }, indicating that the first array element is a #DIV/0! error and the second array element is an #N/A error:

If the array is itself an error, ERROR.TYPE returns an array with a single element. This formula returns { 4 }{ 4 }, indicating a #REF! error due to the arrays not being the same size (which GROWTH requires):