ERROR.TYPE function

ERROR.TYPE returns a number 1–7 identifying the type of error, letting you handle different errors individually or display a helpful message to users.

=ERROR.TYPE(error_val)

Generate a ERROR.TYPE formula

Describe what you need. The generator will reach for ERROR.TYPE where ERROR.TYPE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How ERROR.TYPE reads its arguments
error_valrequiredERROR.TYPE →
ArgumentRequiredDescription
error_valRequiredThe value to check, which should be an error (like #N/A, #DIV/0!, #REF!, #VALUE!, #NUM!, #NAME?, #NULL!) or a formula result that might produce one; if it's a normal value, ERROR.TYPE returns #N/A.

Returns

A number from 1 to 7 corresponding to the error type, or #N/A if the argument is not an error.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Identify when a campaign lookup fails

CampaignChannelSpendClicksConversions
Spring2025Google Ads500025025
Spring2025Facebook300018015
Summer2025Google Ads600032035
Summer2025Email5009512
=ERROR.TYPE(VLOOKUP("Q4Campaign",A$2:A$5,1,0))

Result: 5

VLOOKUP searches for "Q4Campaign" in the campaign column (A2:A5) but doesn't find it, returning #N/A. ERROR.TYPE(#N/A) equals 5, so you know the lookup failed and can respond accordingly.

2. Detect division-by-zero in cost-per-click calculation

CampaignChannelSpendClicksConversions
Spring2025Google Ads500025025
Spring2025Facebook300018015
Summer2025Google Ads600032035
Summer2025Email5009512
Q3CampaignDisplay1500010
=ERROR.TYPE(C5/D5)

Result: 2

Row 5 has 0 clicks. Dividing spend (1500) by clicks (0) produces #DIV/0!. ERROR.TYPE(#DIV/0!) equals 2, which tells you the error is specifically a division problem, not a missing value.

3. Identify type mismatch in text search function

CampaignChannelSpendClicksConversions
Spring2025Google Ads500025025
Spring2025Facebook300018015
Summer2025Google Ads600032035
Summer2025Email5009512
=ERROR.TYPE(SEARCH(D2,A2))

Result: 3

SEARCH expects text arguments, but D2 contains a number (250). Passing a number where text is required produces #VALUE!. ERROR.TYPE(#VALUE!) equals 3, distinguishing type errors from missing-value or division errors.

Common errors

Which ERROR.TYPE error are you seeing?
ERROR.TYPE returned an error#N/A
Check the value with ISERROR() first, or wrap the formula in IFERROR(ERROR.TYPE(...), 0) to return a default number for non-errors.
#REF!
Verify the ranges in your formula still exist; update cell references if columns or rows were moved or deleted from the source data.
#NAME?
Confirm your spreadsheet supports ERROR.TYPE (Excel 2007+ and Google Sheets do); double-check the spelling and ensure you're not in an environment that doesn't support it.
ErrorWhy it happensHow to fix it
#N/AERROR.TYPE was called on a normal value (number, text, or successful formula result) instead of an error value.Check the value with ISERROR() first, or wrap the formula in IFERROR(ERROR.TYPE(...), 0) to return a default number for non-errors.
#REF!The formula in error_val references a cell or range that no longer exists (e.g., a deleted column).Verify the ranges in your formula still exist; update cell references if columns or rows were moved or deleted from the source data.
#NAME?ERROR.TYPE is not recognized; the function may be unavailable in your spreadsheet software or misspelled in the formula.Confirm your spreadsheet supports ERROR.TYPE (Excel 2007+ and Google Sheets do); double-check the spelling and ensure you're not in an environment that doesn't support it.

Tips and when to use something else

  • ERROR.TYPE returns #N/A for non-error values—not 0. Use IFERROR(ERROR.TYPE(...), 0) if you want a numeric default for non-errors.
  • Combine ERROR.TYPE with IF or SWITCH to handle different errors distinctly: =SWITCH(ERROR.TYPE(formula), 2, "Divide by zero", 5, "Not found", "Other error").
  • Use ISERROR() to check if a value is an error before calling ERROR.TYPE; this prevents unnecessary #N/A results when values are already validated.
  • For simple error handling without identifying the type, use IFERROR() instead; reserve ERROR.TYPE for when you need to respond differently to different error types.

Related functions

IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.ISERRORISERROR returns TRUE if a value is an error like #N/A or #REF!, FALSE otherwise, enabling error detection and handling.ISNAISNA detects whether a value is the #N/A error, typically returned when a lookup function fails to find a match.NAReturns the #N/A error value, used to explicitly mark cells or formulas with data that is unavailable or unknown.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

What does ERROR.TYPE return for a normal number or text value?
ERROR.TYPE returns #N/A. The function only works on error values; if you pass a number, text, or successful formula result, it signals that the input is not an error by returning #N/A.
How do I use ERROR.TYPE to respond differently to different error types?
Wrap ERROR.TYPE in IF or SWITCH: =SWITCH(ERROR.TYPE(formula), 2, "Division by zero", 5, "Value not found", 6, "Broken reference", "Other error"). This lets you display custom messages for each error type.
Can ERROR.TYPE identify #SPILL! or #CALC! errors?
ERROR.TYPE returns numbers 1–7 for standard errors; #SPILL! and #CALC! are newer errors in some versions. If you encounter these, use ISERROR() to confirm they're errors, then inspect the cell to see the symbol directly.
Why does ERROR.TYPE return #N/A when my formula is successful?
ERROR.TYPE only returns numbers 1–7 when the input is an error; if the formula works without error, ERROR.TYPE returns #N/A to indicate "this is not an error." Use IFERROR to provide a fallback value instead.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17