Yesterday, in the 30XL30D challenge, we found values with the LOOKUP function, and we’ll use that function again today, when working with error results.
ERROR.TYPE Function
For day 17 in the challenge, we’ll examine the ERROR.TYPE function. It can identify specific types of errors, and you can use that information to help with troubleshooting.
So, let’s take a look at the ERROR.TYPE information and examples, and if you have other tips or examples, please share them in the comments.
Watch the ERROR.TYPE Video
To see a demonstration of the examples in the ERROR.TYPE function sample workbook, you can watch this short Excel video tutorial.
Function 17: ERROR.TYPE
The ERROR.TYPE function identifies an error type by number, or returns #N/A if no error is found.
How Could You Use ERROR.TYPE?
With the ERROR.TYPE function, you can:
- identify an error type
- help users troubleshoot error results
ERROR.TYPE Syntax
The ERROR.TYPE function has the following syntax:
- ERROR.TYPE(error_val)
- error_val is the error that you want to identify
- ERROR.TYPE codes:
- 1…..#NULL!
- 2…..#DIV/0!
- 3…..#VALUE!
- 4…..#REF!
- 5…..#NAME?
- 6…..#NUM!
- 7…..#N/A
- #N/A..Other
ERROR.TYPE Traps
If the error_val is not an error, the result of the ERROR.TYPE function is an #N/A error. You can avoid this, by using ISERROR to test for an error, as shown in Example 2.
Example 1: Identify the Error Type
With the ERROR.TYPE function you can check a cell, to identify which error it contains.
If there isn’t an error in the cell, the result is #N/A, instead of an error type code number.
=ERROR.TYPE(B3)
In this example, cell B3 contains #VALUE!, so the error type is 3.
Example 2: Help Users Troubleshoot Errors
By combining the ERROR.TYPE function with other functions, you can help users troubleshoot error results in a cell.
In this example, numbers should be entered in cells B3 and C3.
- If text is entered, the result in D3 is a #VALUE! error.
- If a zero is entered in cell C3, the result is a #DIV/0! error.
In cell D4, ISERROR checks for an error, and the ERROR.TYPE function returns a number for the error. The LOOKUP function finds the applicable troubleshooting message from a table of error type codes, and displays it.
=IF(ISERROR(D3), LOOKUP(ERROR.TYPE(D3), $B$9:$B$15, $D$9:$D$15),””)
Here’s the lookup table, with Error Type numbers, and messages.
Download the ERROR.TYPE Function File
To see the formulas used in today’s examples, you can go to the Excel Formula Error Values page on my Contextures site.
On that page, go to the Download section, to get the ERROR.TYPE function sample workbook. The file is zipped, and is in xlsx file format, with no macros.
_____________
This can be a useful technique in determining whether or not a given cell resides within a Named Range, eg:
=ISNA(ERROR.TYPE(_test A1))
where _test is a named range and which may be non-contiguous
(the above is susceptible to error pending existence of underlying errors within A1)