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.
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.
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 download the ERROR.TYPE function sample workbook. The file is zipped, and is in Excel 2007 file format.
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.
YouTube link: Troubleshoot Excel Errors with ERROR.TYPE Function
_____________
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)