ERROR.TYPE

 The ERROR.TYPE function in Excel returns a number corresponding to a specific error type. It is useful for identifying and handling errors in formulas. Below is an expanded explanation of the ERROR.TYPE function, including examples with data and formulas.

---

Syntax

ERROR.TYPE(error_val)

• error_val: The error value you want to evaluate. This can be a cell reference or a formula that results in an error.

---

Error Types and Their Corresponding Numbers

Error Type	Number
#NULL!	1
#DIV/0!	2
#VALUE!	3
#REF!	4
#NAME?	5
#NUM!	6
#N/A	7
#GETTING_DATA	8
#SPILL!	9
#UNKNOWN!	10

---

Examples with Data and Formulas

Example 1: #NULL! Error

• Formula: =A1 B1 (Missing operator between A1 and B1)

• Result: #NULL!

• ERROR.TYPE Formula: =ERROR.TYPE(A1 B1)

• Output: 1

---

Example 2: #DIV/0! Error

• Formula: =A2/B2 (Where B2 is 0)

• Result: #DIV/0!

• ERROR.TYPE Formula: =ERROR.TYPE(A2/B2)

• Output: 2

---

Example 3: #VALUE! Error

• Formula: ="Text" + 5 (Adding text to a number)

• Result: #VALUE!

• ERROR.TYPE Formula: =ERROR.TYPE("Text" + 5)

• Output: 3

---

Example 4: #REF! Error

• Formula: =A3 (Where A3 is deleted or invalid)

• Result: #REF!

• ERROR.TYPE Formula: =ERROR.TYPE(A3)

• Output: 4

---

Example 5: #NAME? Error

• Formula: =SUMM(A1:A5) (Misspelled function name)

• Result: #NAME?

• ERROR.TYPE Formula: =ERROR.TYPE(SUMM(A1:A5))

• Output: 5

---

Example 6: #NUM! Error

• Formula: =SQRT(-1) (Invalid numeric operation)

• Result: #NUM!

• ERROR.TYPE Formula: =ERROR.TYPE(SQRT(-1))

• Output: 6

---

Example 7: #N/A Error

• Formula: =VLOOKUP("X", A1:B5, 2, FALSE) (Where "X" is not found)

• Result: #N/A

• ERROR.TYPE Formula: =ERROR.TYPE(VLOOKUP("X", A1:B5, 2, FALSE))

• Output: 7

---

Example 8: #GETTING_DATA Error

• Formula: =A1 (Where A1 is fetching external data and is not yet available)

• Result: #GETTING_DATA

• ERROR.TYPE Formula: =ERROR.TYPE(A1)

• Output: 8

---

Example 9: #SPILL! Error

• Formula: =SEQUENCE(5) (Where the spill range is blocked by other data)

• Result: #SPILL!

• ERROR.TYPE Formula: =ERROR.TYPE(SEQUENCE(5))

• Output: 9

---

Example 10: #UNKNOWN! Error

• Formula: =A1 (Where A1 contains an unrecognized error)

• Result: #UNKNOWN!

• ERROR.TYPE Formula: =ERROR.TYPE(A1)

• Output: 10

---

Practical Use Case

You can use ERROR.TYPE with the IF function to handle errors gracefully. For example:

excel

Copy

=IF(ERROR.TYPE(A1/B1)=2, "Division by zero error", A1/B1)

This formula checks if the result of A1/B1 is a #DIV/0! error (type 2) and displays a custom message. Otherwise, it returns the result of the division.