Exceljet

Quick, clean, and to the point

Excel ERROR.TYPE Function

Excel ERROR.TYPE function
Summary 

The Excel ERROR.TYPE function returns a number that corresponds to a specific error value. You can use ERROR.TYPE to test for specific kinds of errors. If no error exists, ERROR.TYPE returns #N/A. See below for a key to the error codes returned by ERROR.TYPE. 

Purpose 
Test for a specific error value
Return value 
An error number or #N/A if no error.
Syntax 
=ERROR.TYPE (error_val)
Arguments 
  • error_val - The error for which to get an error code.
Version 
Usage notes 

The Excel ERROR.TYPE function returns a number that corresponds to a specific error value. You can use ERROR.TYPE to test for specific kinds of errors. If no error exists, ERROR.TYPE returns #N/A. See the table below for a key to the error codes returned by ERROR.TYPE. 

The ERROR.TYPE function takes just one argument, error_val, which is expected to be an Excel error like #VALUE!, #DIV/0!, #NAME!, etc. When error_val is an error, ERROR.TYPE returns a numeric code. If error_val is not an error, ERROR.TYPE returns an error itself: the #N/A error. In most cases, error_val will be supplied as a reference to a cell that may contain an error value.

Examples

If cell A1 contains displays the #DIV/0 error, then ERROR.TYPE will return 2:

=ERROR.TYPE(A1) // returns 2

If cell A1 displays the #N/A error, ERROR.TYPE returns 7

=ERROR.TYPE(A1) // returns 7

If cell A1 displays no error, ERROR.TYPE returns #N/A

=ERROR.TYPE(A1) // returns #N/A

One way to use ERROR.TYPE is to test for specific errors and display a custom message when certain error conditions exist. For example, to test for a #DIV/0! error in cell A1 and display a custom message when present, you can use a formula like this:

=IF(ISERROR(A1),IF(ERROR.TYPE(A1)=2,"Missing value",A1),"")

This formula returns an empty string ("") when no error is present, and the message "Missing value" when A1 contains #DIV/0!. Other errors are displayed normally.

Error types

Error Error type
#NULL! 1
#DIV/0! 2
#VALUE! 3
#REF! 4
#NAME? 5
#NUM! 6
#N/A 7
#GETTING_DATA 8
#SPILL! 9
#CALC! 14

Other error functions

Excel provides a number of error-related functions, each with a different behavior:

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.