XERROR.TYPE

XERROR.TYPE(reference,[analysis_type],[if_not_error])
reference
Required. Specifies the function input
analysis_type
Optional. Specifies the type of output data and takes one of three arguments: 0 or omitted, for numeric error-type output; 1, for text (descriptive) output; and 2, for numeric error-number output, same as Excel's built-in ERROR.TYPE
if_not_error
Optional. Specifies what output to display for function entries that are not errors and can take a single entry of any data type (i.e. number, text, blank, logical, or error) (#N/A if omitted)
XERROR.TYPE displays the XTYPE-style error type or error name for all Excel errors
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
XERROR.TYPE displays the XTYPE-style error type or error name for all Excel errors

XERROR.TYPE takes three parameters, one requires, and two optional, as follows:
A) The first parameter, required, specifies the input and can be a single cell, range, or array.

B) The second parameter, optional, specifies the type of output data and takes one of three arguments:
0 or omitted, for numeric error-type output
1, for text (descriptive) output
2, for numeric error-number output, same as Excel's built-in ERROR.TYPE

C) The third parameter, optional, specifies what output to display for function entries that are not errors and can take a single entry of any data type (i.e. number, text, blank, logical, or error). If omitted, an #N/A error will be displayed by default

XERROR.TYPE
VBA Code: 
=LAMBDA(reference,[analysis_type],[if_not_error],
   IF(IO(reference),
   NA(),
   LET(
      a,PLS(PO0(analysis_type)),
      o,SWITCH(a,
         0,SWITCH(ERROR.TYPE(reference),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),
         1,SWITCH(ERROR.TYPE(reference),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_Data",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),
         2,ERROR.TYPE(reference)),
      IF(
         ISERROR(a),
         o,
         IF(
            ISNOTARRAY(if_not_error),
            IF(IO(if_not_error),o,IF(ISERROR(o),if_not_error,o)),
            NA()
            )
        )
      )
   )
)

The following helper functions are also used in the code. Full code and descriptions are included in my "Blank power workbook" template, which you can download through the link in my signature below:

IO (shortened form of ISOMITTED)
VBA Code: 
=LAMBDA(parameter,ISOMITTED(parameter))

PLS (limits parameter to only a single entry)
VBA Code: 
=LAMBDA(parameter,JAN(IF(OR(ISARRAY(parameter),ISERRORS(parameter),ISBLANKS(parameter)),NA(),parameter)))

PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
VBA Code: 
=LAMBDA([parameter],IF(IO(parameter),0,parameter))

ISNOTARRAY (Returns TRUE if the reference is not an array [i.e. is a single element or is omitted] and FALSE otherwise)
VBA Code: 
=LAMBDA([reference],[output],LET(r,IFERROR(reference,""),o,PLSL(output),m,IF(IO(reference),TRUE,IF(RBYC(r)=1,TRUE,FALSE)),JAO(m,o)))

XERROR.TYPE.xlsx
ABCDEFGHIJKLMN
1
2#CALC!
3
4#N/A(reference from blank cell)
5#VALUE!(no reference)
6if_not_error
7reference input from cells individuallyreference input from cells as range (for spill output)not error
8analysis_typeanalysis_type
9error typeerroromitted012omitted0120
100#EXTERNAL!160160dt:ERROR_EXTERNAL!0160160dt:ERROR_EXTERNAL!0160
111#NULL!161161dt:ERROR_NULL!1161161dt:ERROR_NULL!1161
122#DIV/0!162162dt:ERROR_DIV/0!2162162dt:ERROR_DIV/0!2162
133#VALUE!163163dt:ERROR_VALUE!3163163dt:ERROR_VALUE!3163
144#REF!164164dt:ERROR_REF!4164164dt:ERROR_REF!4164
155#NAME?165165dt:ERROR_NAME?5165165dt:ERROR_NAME?5165
166#NUM!166166dt:ERROR_NUM!6166166dt:ERROR_NUM!6166
177#N/A167167dt:ERROR_N/A7167167dt:ERROR_N/A7167
188#GETTING_DATA168168dt:ERROR_GETTING_Data8168168dt:ERROR_GETTING_Data8168
1913#FIELD!16131613dt:ERROR_FIELD!1316131613dt:ERROR_FIELD!131613
2014#CALC!16141614dt:ERROR_CALC!1416141614dt:ERROR_CALC!141614
21n/a43#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
22n/abook#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
23n/aTRUE#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
24n/aFALSE#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
25n/a""#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
26
27if_not_error
28reference input directly individuallynot error
29analysis_type
30error typeerroromitted0120
310#EXTERNAL!160160dt:ERROR_EXTERNAL!0160
321#NULL!161161dt:ERROR_NULL!1161
332#DIV/0!162162dt:ERROR_DIV/0!2162
343#VALUE!163163dt:ERROR_VALUE!3163
354#REF!164164dt:ERROR_REF!4164
365#NAME?165165dt:ERROR_NAME?5165
376#NUM!166166dt:ERROR_NUM!6166
387#N/A167167dt:ERROR_N/A7167
398#GETTING_DATA168168dt:ERROR_GETTING_Data8168
40n/a43#N/A#N/A#N/A#N/Anot error
41n/abook#N/A#N/A#N/A#N/Anot error
42n/abook (wihout quotes)165165dt:ERROR_NAME?5165
43n/aTRUE#N/A#N/A#N/A#N/Anot error
44n/aFALSE#N/A#N/A#N/A#N/Anot error
45n/a""#N/A#N/A#N/A#N/Anot error
46
47if_not_error
48reference input directly as array (for spill output)not error
49analysis_type
50error typeerroromitted0120
510#EXTERNAL!160160dt:ERROR_EXTERNAL!0160
521#NULL!161161dt:ERROR_NULL!1161
532#DIV/0!162162dt:ERROR_DIV/0!2162
543#VALUE!163163dt:ERROR_VALUE!3163
554#REF!164164dt:ERROR_REF!4164
565#NAME?165165dt:ERROR_NAME?5165
576#NUM!166166dt:ERROR_NUM!6166
587#N/A167167dt:ERROR_N/A7167
598#GETTING_DATA168168dt:ERROR_GETTING_Data8168
60n/a43#N/A#N/A#N/A#N/Anot error
61n/abook#N/A#N/A#N/A#N/Anot error
62n/aTRUE#N/A#N/A#N/A#N/Anot error
63n/aFALSE#N/A#N/A#N/A#N/Anot error
64n/a""#N/A#N/A#N/A#N/Anot error
65
Sheet1
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[analysis_type],[if_not_error],IF(IO(reference),NA(),LET(a,PLS(PO0(analysis_type)),o,SWITCH(a,0,SWITCH(ERROR.TYPE(reference),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),1,SWITCH(ERROR.TYPE(reference),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_Data",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),2,ERROR.TYPE(reference)),IF(ISERROR(a),o,IF(ISNOTARRAY(if_not_error),IF(IO(if_not_error),o,IF(ISERROR(o),if_not_error,o)),NA())))))
C4,D10:D25C4=XERROR.TYPE(B4)
C5C5=XERROR.TYPE()
E10:E25E10=XERROR.TYPE(C10,0)
F10:F25F10=XERROR.TYPE(C10,1)
G10:G25G10=XERROR.TYPE(C10,2)
H10:H25H10=XERROR.TYPE(C10:C25)
I10:I25I10=XERROR.TYPE(C10:C25,0)
J10:J25J10=XERROR.TYPE(C10:C25,1)
K10:K25K10=XERROR.TYPE(C10:C25,2)
C10:C20,C51:C59,C31:C39C10=XERROR(B10)
M10M10=XERROR.TYPE(C10,0,M7)
M11M11=XERROR.TYPE(C11,0,M7)
M12M12=XERROR.TYPE(C12,0,M7)
M13M13=XERROR.TYPE(C13,0,M7)
M14M14=XERROR.TYPE(C14,0,M7)
M15M15=XERROR.TYPE(C15,0,M7)
M16M16=XERROR.TYPE(C16,0,M7)
M17M17=XERROR.TYPE(C17,0,M7)
M18M18=XERROR.TYPE(C18,0,M7)
M19M19=XERROR.TYPE(C19,0,M7)
M20M20=XERROR.TYPE(C20,0,M7)
M21M21=XERROR.TYPE(C21,0,M7)
M22M22=XERROR.TYPE(C22,0,M7)
M23M23=XERROR.TYPE(C23,0,M7)
M24M24=XERROR.TYPE(C24,0,M7)
M25M25=XERROR.TYPE(C25,0,M7)
D31D31=XERROR.TYPE(#EXTERNAL!)
E31E31=XERROR.TYPE(#EXTERNAL!,0)
F31F31=XERROR.TYPE(#EXTERNAL!,1)
G31G31=XERROR.TYPE(#EXTERNAL!,2)
D32D32=XERROR.TYPE(#NULL!)
E32E32=XERROR.TYPE(#NULL!,0)
F32F32=XERROR.TYPE(#NULL!,1)
G32G32=XERROR.TYPE(#NULL!,2)
D33D33=XERROR.TYPE(#DIV/0!)
E33E33=XERROR.TYPE(#DIV/0!,0)
F33F33=XERROR.TYPE(#DIV/0!,1)
G33G33=XERROR.TYPE(#DIV/0!,2)
D34D34=XERROR.TYPE(#VALUE!)
E34E34=XERROR.TYPE(#VALUE!,0)
F34F34=XERROR.TYPE(#VALUE!,1)
G34G34=XERROR.TYPE(#VALUE!,2)
D35D35=XERROR.TYPE(#REF!)
E35E35=XERROR.TYPE(#REF!,0)
F35F35=XERROR.TYPE(#REF!,1)
G35G35=XERROR.TYPE(#REF!,2)
D36D36=XERROR.TYPE(#NAME?)
E36E36=XERROR.TYPE(#NAME?,0)
F36F36=XERROR.TYPE(#NAME?,1)
G36G36=XERROR.TYPE(#NAME?,2)
D37D37=XERROR.TYPE(#NUM!)
E37E37=XERROR.TYPE(#NUM!,0)
F37F37=XERROR.TYPE(#NUM!,1)
G37G37=XERROR.TYPE(#NUM!,2)
D38D38=XERROR.TYPE(#N/A)
E38E38=XERROR.TYPE(#N/A,0)
F38F38=XERROR.TYPE(#N/A,1)
G38G38=XERROR.TYPE(#N/A,2)
D39D39=XERROR.TYPE(#GETTING_DATA)
E39E39=XERROR.TYPE(#GETTING_DATA,0)
F39F39=XERROR.TYPE(#GETTING_DATA,1)
G39G39=XERROR.TYPE(#GETTING_DATA,2)
D40D40=XERROR.TYPE(43)
E40E40=XERROR.TYPE(43,0)
F40F40=XERROR.TYPE(43,1)
G40G40=XERROR.TYPE(43,2)
D41D41=XERROR.TYPE("book")
E41E41=XERROR.TYPE("book",0)
F41F41=XERROR.TYPE("book",1)
G41G41=XERROR.TYPE("book",2)
D42D42=XERROR.TYPE(book)
E42E42=XERROR.TYPE(book,0)
F42F42=XERROR.TYPE(book,1)
G42G42=XERROR.TYPE(book,2)
D43D43=XERROR.TYPE(TRUE)
E43E43=XERROR.TYPE(TRUE,0)
F43F43=XERROR.TYPE(TRUE,1)
G43G43=XERROR.TYPE(TRUE,2)
D44D44=XERROR.TYPE(FALSE)
E44E44=XERROR.TYPE(FALSE,0)
F44F44=XERROR.TYPE(FALSE,1)
G44G44=XERROR.TYPE(FALSE,2)
D45D45=XERROR.TYPE("")
E45E45=XERROR.TYPE("",0)
F45F45=XERROR.TYPE("",1)
G45G45=XERROR.TYPE("",2)
I31I31=XERROR.TYPE(#EXTERNAL!,0,I28)
I32I32=XERROR.TYPE(#NULL!,0,I28)
I33I33=XERROR.TYPE(#DIV/0!,0,I28)
I34I34=XERROR.TYPE(#VALUE!,0,I28)
I35I35=XERROR.TYPE(#REF!,0,I28)
I36I36=XERROR.TYPE(#NAME?,0,I28)
I37I37=XERROR.TYPE(#NUM!,0,I28)
I38I38=XERROR.TYPE(#N/A,0,I28)
I39I39=XERROR.TYPE(#GETTING_DATA,0,I28)
I40I40=XERROR.TYPE(43,0,I28)
I41I41=XERROR.TYPE("book",0,I28)
I42I42=XERROR.TYPE(book,0,I28)
I43I43=XERROR.TYPE(TRUE,0,I28)
I44I44=XERROR.TYPE(FALSE,0,I28)
I45I45=XERROR.TYPE("",0,I28)
D51:D64D51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""})
E51:E64E51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},0)
F51:F64F51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},1)
G51:G64G51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},2)
I51:I64I51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},0,I48)
Dynamic array formulas.
 
Upvote 0
Quick update for the code:

XERROR.TYPE
VBA Code: 
=LAMBDA(reference,[analysis_type],[if_not_error],
   IF(
      IO(reference),
      NA(),
      LET(
         a,PLS(PO0(analysis_type)),
         o,SWITCH(a,0,SWITCH(ERROR.TYPE(reference),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),1,SWITCH(ERROR.TYPE(reference),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_Data",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),2,ERROR.TYPE(reference)),
         IF(
            ISERROR(a),
            o,
            IF(
               ISARRAY(if_not_error),
               NA(),
               IF(IO(if_not_error),o,IF(ISERROR(o),if_not_error,o))
            )
         )
      )
   )
)

IO (shortened form of ISOMITTED)
VBA Code: 
=LAMBDA(parameter,ISOMITTED(parameter))

PLS (limits parameter to only a single entry)
VBA Code: 
=LAMBDA(parameter,INDEX(IF(OR(ISARRAY(parameter),ISERRORS(parameter),ISBLANKS(parameter)),NA(),parameter),1))

PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
VBA Code: 
=LAMBDA([parameter],IF(IO(parameter),0,parameter))

ISARRAY (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise)
VBA Code: 
=LAMBDA([reference],[output],LET(r,IFERROR(IF(reference="","",reference),""),o,PLSL(output),m,IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE)),IF(m,OT(o),OF(o))))

PLSL (Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or omitted; and #N/A otherwise)
VBA Code: 
=LAMBDA([parameter],IF(IO(parameter),0,IF(ISARRAY(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))

T1F0 (Returns 1 if the reference is TRUE and 0 if the reference is FALSE)
VBA Code: 
=LAMBDA(reference,[analysis_type],LET(a,PLSL(analysis_type),b,IF(ISNUMBER(reference)+ISLOGICAL(reference),reference,SWITCH(a,0,NA(),1,0)),IF(b,1,0)))

XERROR.TYPE.xlsx
ABCDEFGHIJKLMN
1
2#CALC!
3
4#N/A(reference from blank cell)
5#VALUE!(no reference)
6if_not_error
7reference input from cells individuallyreference input from cells as range (for spill output)not error
8analysis_typeanalysis_type
9error typeerroromitted012omitted0120
100#EXTERNAL!160160dt:ERROR_EXTERNAL!0160160dt:ERROR_EXTERNAL!0160
111#NULL!161161dt:ERROR_NULL!1161161dt:ERROR_NULL!1161
122#DIV/0!162162dt:ERROR_DIV/0!2162162dt:ERROR_DIV/0!2162
133#VALUE!163163dt:ERROR_VALUE!3163163dt:ERROR_VALUE!3163
144#REF!164164dt:ERROR_REF!4164164dt:ERROR_REF!4164
155#NAME?165165dt:ERROR_NAME?5165165dt:ERROR_NAME?5165
166#NUM!166166dt:ERROR_NUM!6166166dt:ERROR_NUM!6166
177#N/A167167dt:ERROR_N/A7167167dt:ERROR_N/A7167
188#GETTING_DATA168168dt:ERROR_GETTING_Data8168168dt:ERROR_GETTING_Data8168
1913#FIELD!16131613dt:ERROR_FIELD!1316131613dt:ERROR_FIELD!131613
2014#CALC!16141614dt:ERROR_CALC!1416141614dt:ERROR_CALC!141614
21n/a43#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
22n/abook#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
23n/aTRUE#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
24n/aFALSE#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
25n/a""#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
26
27if_not_error
28reference input directly individuallynot error
29analysis_type
30error typeerroromitted0120
310#EXTERNAL!160160dt:ERROR_EXTERNAL!0160
321#NULL!161161dt:ERROR_NULL!1161
332#DIV/0!162162dt:ERROR_DIV/0!2162
343#VALUE!163163dt:ERROR_VALUE!3163
354#REF!164164dt:ERROR_REF!4164
365#NAME?165165dt:ERROR_NAME?5165
376#NUM!166166dt:ERROR_NUM!6166
387#N/A167167dt:ERROR_N/A7167
398#GETTING_DATA168168dt:ERROR_GETTING_Data8168
40n/a43#N/A#N/A#N/A#N/Anot error
41n/abook#N/A#N/A#N/A#N/Anot error
42n/abook (wihout quotes)165165dt:ERROR_NAME?5165
43n/aTRUE#N/A#N/A#N/A#N/Anot error
44n/aFALSE#N/A#N/A#N/A#N/Anot error
45n/a""#N/A#N/A#N/A#N/Anot error
46
47if_not_error
48reference input directly as array (for spill output)not error
49analysis_type
50error typeerroromitted0120
510#EXTERNAL!160160dt:ERROR_EXTERNAL!0160
521#NULL!161161dt:ERROR_NULL!1161
532#DIV/0!162162dt:ERROR_DIV/0!2162
543#VALUE!163163dt:ERROR_VALUE!3163
554#REF!164164dt:ERROR_REF!4164
565#NAME?165165dt:ERROR_NAME?5165
576#NUM!166166dt:ERROR_NUM!6166
587#N/A167167dt:ERROR_N/A7167
598#GETTING_DATA168168dt:ERROR_GETTING_Data8168
60n/a43#N/A#N/A#N/A#N/Anot error
61n/abook#N/A#N/A#N/A#N/Anot error
62n/aTRUE#N/A#N/A#N/A#N/Anot error
63n/aFALSE#N/A#N/A#N/A#N/Anot error
64n/a""#N/A#N/A#N/A#N/Anot error
65
Sheet1
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[analysis_type],[if_not_error],IF(IO(reference),NA(),LET(a,PLS(PO0(analysis_type)),o,SWITCH(a,0,SWITCH(ERROR.TYPE(reference),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),1,SWITCH(ERROR.TYPE(reference),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_Data",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),2,ERROR.TYPE(reference)),IF(ISERROR(a),o,IF(ISARRAY(if_not_error),NA(),IF(IO(if_not_error),o,IF(ISERROR(o),if_not_error,o)))))))
C4,D10:D25C4=XERROR.TYPE(B4)
C5C5=XERROR.TYPE()
E10:E25E10=XERROR.TYPE(C10,0)
F10:F25F10=XERROR.TYPE(C10,1)
G10:G25G10=XERROR.TYPE(C10,2)
H10:H25H10=XERROR.TYPE(C10:C25)
I10:I25I10=XERROR.TYPE(C10:C25,0)
J10:J25J10=XERROR.TYPE(C10:C25,1)
K10:K25K10=XERROR.TYPE(C10:C25,2)
C10:C20,C51:C59,C31:C39C10=XERROR(B10)
M10M10=XERROR.TYPE(C10,0,M7)
M11M11=XERROR.TYPE(C11,0,M7)
M12M12=XERROR.TYPE(C12,0,M7)
M13M13=XERROR.TYPE(C13,0,M7)
M14M14=XERROR.TYPE(C14,0,M7)
M15M15=XERROR.TYPE(C15,0,M7)
M16M16=XERROR.TYPE(C16,0,M7)
M17M17=XERROR.TYPE(C17,0,M7)
M18M18=XERROR.TYPE(C18,0,M7)
M19M19=XERROR.TYPE(C19,0,M7)
M20M20=XERROR.TYPE(C20,0,M7)
M21M21=XERROR.TYPE(C21,0,M7)
M22M22=XERROR.TYPE(C22,0,M7)
M23M23=XERROR.TYPE(C23,0,M7)
M24M24=XERROR.TYPE(C24,0,M7)
M25M25=XERROR.TYPE(C25,0,M7)
D31D31=XERROR.TYPE(#EXTERNAL!)
E31E31=XERROR.TYPE(#EXTERNAL!,0)
F31F31=XERROR.TYPE(#EXTERNAL!,1)
G31G31=XERROR.TYPE(#EXTERNAL!,2)
D32D32=XERROR.TYPE(#NULL!)
E32E32=XERROR.TYPE(#NULL!,0)
F32F32=XERROR.TYPE(#NULL!,1)
G32G32=XERROR.TYPE(#NULL!,2)
D33D33=XERROR.TYPE(#DIV/0!)
E33E33=XERROR.TYPE(#DIV/0!,0)
F33F33=XERROR.TYPE(#DIV/0!,1)
G33G33=XERROR.TYPE(#DIV/0!,2)
D34D34=XERROR.TYPE(#VALUE!)
E34E34=XERROR.TYPE(#VALUE!,0)
F34F34=XERROR.TYPE(#VALUE!,1)
G34G34=XERROR.TYPE(#VALUE!,2)
D35D35=XERROR.TYPE(#REF!)
E35E35=XERROR.TYPE(#REF!,0)
F35F35=XERROR.TYPE(#REF!,1)
G35G35=XERROR.TYPE(#REF!,2)
D36D36=XERROR.TYPE(#NAME?)
E36E36=XERROR.TYPE(#NAME?,0)
F36F36=XERROR.TYPE(#NAME?,1)
G36G36=XERROR.TYPE(#NAME?,2)
D37D37=XERROR.TYPE(#NUM!)
E37E37=XERROR.TYPE(#NUM!,0)
F37F37=XERROR.TYPE(#NUM!,1)
G37G37=XERROR.TYPE(#NUM!,2)
D38D38=XERROR.TYPE(#N/A)
E38E38=XERROR.TYPE(#N/A,0)
F38F38=XERROR.TYPE(#N/A,1)
G38G38=XERROR.TYPE(#N/A,2)
D39D39=XERROR.TYPE(#GETTING_DATA)
E39E39=XERROR.TYPE(#GETTING_DATA,0)
F39F39=XERROR.TYPE(#GETTING_DATA,1)
G39G39=XERROR.TYPE(#GETTING_DATA,2)
D40D40=XERROR.TYPE(43)
E40E40=XERROR.TYPE(43,0)
F40F40=XERROR.TYPE(43,1)
G40G40=XERROR.TYPE(43,2)
D41D41=XERROR.TYPE("book")
E41E41=XERROR.TYPE("book",0)
F41F41=XERROR.TYPE("book",1)
G41G41=XERROR.TYPE("book",2)
D42D42=XERROR.TYPE(book)
E42E42=XERROR.TYPE(book,0)
F42F42=XERROR.TYPE(book,1)
G42G42=XERROR.TYPE(book,2)
D43D43=XERROR.TYPE(TRUE)
E43E43=XERROR.TYPE(TRUE,0)
F43F43=XERROR.TYPE(TRUE,1)
G43G43=XERROR.TYPE(TRUE,2)
D44D44=XERROR.TYPE(FALSE)
E44E44=XERROR.TYPE(FALSE,0)
F44F44=XERROR.TYPE(FALSE,1)
G44G44=XERROR.TYPE(FALSE,2)
D45D45=XERROR.TYPE("")
E45E45=XERROR.TYPE("",0)
F45F45=XERROR.TYPE("",1)
G45G45=XERROR.TYPE("",2)
I31I31=XERROR.TYPE(#EXTERNAL!,0,I28)
I32I32=XERROR.TYPE(#NULL!,0,I28)
I33I33=XERROR.TYPE(#DIV/0!,0,I28)
I34I34=XERROR.TYPE(#VALUE!,0,I28)
I35I35=XERROR.TYPE(#REF!,0,I28)
I36I36=XERROR.TYPE(#NAME?,0,I28)
I37I37=XERROR.TYPE(#NUM!,0,I28)
I38I38=XERROR.TYPE(#N/A,0,I28)
I39I39=XERROR.TYPE(#GETTING_DATA,0,I28)
I40I40=XERROR.TYPE(43,0,I28)
I41I41=XERROR.TYPE("book",0,I28)
I42I42=XERROR.TYPE(book,0,I28)
I43I43=XERROR.TYPE(TRUE,0,I28)
I44I44=XERROR.TYPE(FALSE,0,I28)
I45I45=XERROR.TYPE("",0,I28)
D51:D64D51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""})
E51:E64E51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},0)
F51:F64F51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},1)
G51:G64G51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},2)
I51:I64I51=XERROR.TYPE({#EXTERNAL!;#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},0,I48)
Dynamic array formulas.
 
Updated code: addresses the recent changes in the error types and their associated numbers

XERROR.TYYPE
Excel Formula: 
=LAMBDA(reference,[analysis_type],[if_not_error],
   IF(
      IO(reference),
      NA(),
      LET(
         a,PLS(PO0(analysis_type)),
         o,SWITCH(a,
            0,SWITCH(ERROR.TYPE(reference),1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614,19,1619),
            1,SWITCH(ERROR.TYPE(reference),1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_Data",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!",19,"dt:ERROR_PYTHON!"),
            2,ERROR.TYPE(reference)),
         IF(
            ISERROR(a),
            o,
            IF(
               JAN(if_not_error),
               NA(),
               IF(
                  IO(if_not_error),
                  o,
                  IF(
                     ISERROR(o),
                     if_not_error,
                     o
                  )
               )
            )
         )
      )
   )
)

IO (shortened form of ISOMITTED)
Excel Formula: 
=LAMBDA(parameter,ISOMITTED(parameter))

PLS (limits parameter to only a single entry)
Excel Formula: 
=LAMBDA(parameter,
   INDEX(
      IF(
         OR(JAN(parameter),JAO(parameter),JAP(parameter)),
         NA(),
         parameter
         ),
      1
   )
)

PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
Excel Formula: 
=LAMBDA([parameter],
   IF(
      IO(parameter),
      0,
      parameter
   )
)

JAN (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise)
Excel Formula: 
=LAMBDA([reference],
   LET(
      r,IFERROR(IF(reference="","",reference),""),
      IF(
         IO(reference),
         FALSE,
         IF(
            ROWS(r)*COLUMNS(r)>1,
            TRUE,
            FALSE
         )
      )
   )
)


XERROR.TYPE2.xlsx
ABCDEFGHIJKLMNOPQ
1
2#CALC!basic types
3if_not_error1dt:NUMBER
4reference input from cells individuallyreference input from cells as range (for spill output)not error2dt:TEXT
5analysis_typeanalysis_typeanalysis_type3dt:BLANK
6error typeerroromitted012omitted01204dt:LOGICAL
71#NULL!161161dt:ERROR_NULL!1161161dt:ERROR_NULL!116116dt:ERROR
82#DIV/0!162162dt:ERROR_DIV/0!2162162dt:ERROR_DIV/0!2162
93#VALUE!163163dt:ERROR_VALUE!3163163dt:ERROR_VALUE!3163advanced types
104#REF!164164dt:ERROR_REF!4164164dt:ERROR_REF!41641dt:NUMBER
115#NAME?165165dt:ERROR_NAME?5165165dt:ERROR_NAME?51652dt:TEXT
126#NUM!166166dt:ERROR_NUM!6166166dt:ERROR_NUM!616631dt:BLANK_REAL
137#N/A167167dt:ERROR_N/A7167167dt:ERROR_N/A716732dt:BLANK_FORMULA
148#GETTING_DATA168168dt:ERROR_GETTING_Data8168168dt:ERROR_GETTING_Data816833dt:BLANK_SPACE
159#SPILL!169169dt:ERROR_SPILL!9169169dt:ERROR_SPILL!916934dt:BLANK_PASTED
1610#CONNECT!16101610dt:ERROR_CONNECT!1016101610dt:ERROR_CONNECT!10161041dt:LOGICAL_TRUE
1711#BLOCKED!16111611dt:ERROR_BLOCKED!1116111611dt:ERROR_BLOCKED!11161142dt:LOGICAL_FALSE
1812#UNKNOWN!16121612dt:ERROR_UNKNOWN!1216121612dt:ERROR_UNKNOWN!121612161dt:ERROR_NULL!
1913#FIELD!16131613dt:ERROR_FIELD!1316131613dt:ERROR_FIELD!131613162dt:ERROR_DIV/0!
2014#CALC!16141614dt:ERROR_CALC!1416141614dt:ERROR_CALC!141614163dt:ERROR_VALUE!
21n/a43#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error164dt:ERROR_REF!
22n/abook#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error165dt:ERROR_NAME?
23n/aTRUE#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error166dt:ERROR_NUM!
24n/aFALSE#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error167dt:ERROR_N/A
25168dt:ERROR_GETTING_DATA
26if_not_error169dt:ERROR_SPILL!
27reference input directly individuallyreference input directly as array (for spill output)not error1610dt:ERROR_CONNECT!
28analysis_typeanalysis_typeanalysis_type1611dt:ERROR_BLOCKED!
29error typeomitted012omitted01201612dt:ERROR_UNKNOWN!
301161161dt:ERROR_NULL!1161161dt:ERROR_NULL!11611613dt:ERROR_FIELD!
312162162dt:ERROR_DIV/0!2162162dt:ERROR_DIV/0!21621614dt:ERROR_CALC!
323163163dt:ERROR_VALUE!3163163dt:ERROR_VALUE!31631619dt:ERROR_PYTHON!
334164164dt:ERROR_REF!4164164dt:ERROR_REF!4164
345165165dt:ERROR_NAME?5165165dt:ERROR_NAME?5165
356166166dt:ERROR_NUM!6166166dt:ERROR_NUM!6166
367167167dt:ERROR_N/A7167167dt:ERROR_N/A7167
378168168dt:ERROR_GETTING_Data8168168dt:ERROR_GETTING_Data8168
38n/a#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
39n/a#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
40n/a#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
41n/a#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
42n/a#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Anot error
43n/a165165dt:ERROR_NAME?5
44
45parameteroutputblank parameterstypeoutput
46no parameter#VALUE!31#N/A
47blank parameter#N/A 32#N/A
48blank cell parameter#N/A 33#N/A
4934#N/A
50
XERROR.TYPE
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[analysis_type],[if_not_error],IF(IO(reference),NA(),LET(a,PLS(PO0(analysis_type)),o,SWITCH(a,0,SWITCH(ERROR.TYPE(reference),1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614,19,1619),1,SWITCH(ERROR.TYPE(reference),1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_Data",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!",19,"dt:ERROR_PYTHON!"),2,ERROR.TYPE(reference)),IF(ISERROR(a),o,IF(JAN(if_not_error),NA(),IF(IO(if_not_error),o,IF(ISERROR(o),if_not_error,o)))))))
D7:D24D7=XERROR.TYPE(C7)
E7:E24E7=XERROR.TYPE(C7,0)
F7:F24F7=XERROR.TYPE(C7,1)
G7:G24G7=XERROR.TYPE(C7,2)
H7:H24H7=XERROR.TYPE(C7:C24)
I7:I24I7=XERROR.TYPE(C7:C24,0)
J7:J24J7=XERROR.TYPE(C7:C24,1)
K7:K24K7=XERROR.TYPE(C7:C24,2)
M7M7=XERROR.TYPE(C7,0,M4)
M8M8=XERROR.TYPE(C8,0,M4)
M9M9=XERROR.TYPE(C9,0,M4)
M10M10=XERROR.TYPE(C10,0,M4)
M11M11=XERROR.TYPE(C11,0,M4)
M12M12=XERROR.TYPE(C12,0,M4)
M13M13=XERROR.TYPE(C13,0,M4)
M14M14=XERROR.TYPE(C14,0,M4)
M15M15=XERROR.TYPE(C15,0,M4)
M16M16=XERROR.TYPE(C16,0,M4)
M17M17=XERROR.TYPE(C17,0,M4)
M18M18=XERROR.TYPE(C18,0,M4)
M19M19=XERROR.TYPE(C19,0,M4)
M20M20=XERROR.TYPE(C20,0,M4)
M21M21=XERROR.TYPE(C21,0,M4)
M22M22=XERROR.TYPE(C22,0,M4)
M23M23=XERROR.TYPE(C23,0,M4)
M24M24=XERROR.TYPE(C24,0,M4)
D30D30=XERROR.TYPE(#NULL!)
E30E30=XERROR.TYPE(#NULL!,0)
F30F30=XERROR.TYPE(#NULL!,1)
G30G30=XERROR.TYPE(#NULL!,2)
H30:H42H30=XERROR.TYPE({#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""})
I30:I42I30=XERROR.TYPE({#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},0)
J30:J42J30=XERROR.TYPE({#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},1)
K30:K42K30=XERROR.TYPE({#NULL!;#DIV/0!;#VALUE!;#REF!;#NAME?;#NUM!;#N/A;#GETTING_DATA;43;"book";TRUE;FALSE;""},2)
D31D31=XERROR.TYPE(#DIV/0!)
E31E31=XERROR.TYPE(#DIV/0!,0)
F31F31=XERROR.TYPE(#DIV/0!,1)
G31G31=XERROR.TYPE(#DIV/0!,2)
D32D32=XERROR.TYPE(#VALUE!)
E32E32=XERROR.TYPE(#VALUE!,0)
F32F32=XERROR.TYPE(#VALUE!,1)
G32G32=XERROR.TYPE(#VALUE!,2)
D33D33=XERROR.TYPE(#REF!)
E33E33=XERROR.TYPE(#REF!,0)
F33F33=XERROR.TYPE(#REF!,1)
G33G33=XERROR.TYPE(#REF!,2)
D34D34=XERROR.TYPE(#NAME?)
E34E34=XERROR.TYPE(#NAME?,0)
F34F34=XERROR.TYPE(#NAME?,1)
G34G34=XERROR.TYPE(#NAME?,2)
D35D35=XERROR.TYPE(#NUM!)
E35E35=XERROR.TYPE(#NUM!,0)
F35F35=XERROR.TYPE(#NUM!,1)
G35G35=XERROR.TYPE(#NUM!,2)
D36D36=XERROR.TYPE(#N/A)
E36E36=XERROR.TYPE(#N/A,0)
F36F36=XERROR.TYPE(#N/A,1)
G36G36=XERROR.TYPE(#N/A,2)
D37D37=XERROR.TYPE(#GETTING_DATA)
E37E37=XERROR.TYPE(#GETTING_DATA,0)
F37F37=XERROR.TYPE(#GETTING_DATA,1)
G37G37=XERROR.TYPE(#GETTING_DATA,2)
D38D38=XERROR.TYPE(43)
E38E38=XERROR.TYPE(43,0)
F38F38=XERROR.TYPE(43,1)
G38G38=XERROR.TYPE(43,2)
D39D39=XERROR.TYPE("book")
E39E39=XERROR.TYPE("book",0)
F39F39=XERROR.TYPE("book",1)
G39G39=XERROR.TYPE("book",2)
D40D40=XERROR.TYPE(TRUE)
E40E40=XERROR.TYPE(TRUE,0)
F40F40=XERROR.TYPE(TRUE,1)
G40G40=XERROR.TYPE(TRUE,2)
D41D41=XERROR.TYPE(FALSE)
E41E41=XERROR.TYPE(FALSE,0)
F41F41=XERROR.TYPE(FALSE,1)
G41G41=XERROR.TYPE(FALSE,2)
D42,C47D42=XERROR.TYPE("")
E42E42=XERROR.TYPE("",0)
F42F42=XERROR.TYPE("",1)
G42G42=XERROR.TYPE("",2)
D43D43=XERROR.TYPE(book)
E43E43=XERROR.TYPE(book,0)
F43F43=XERROR.TYPE(book,1)
G43G43=XERROR.TYPE(book,2)
M30M30=XERROR.TYPE(#NULL!,0,M27)
M31M31=XERROR.TYPE(#DIV/0!,0,M27)
M32M32=XERROR.TYPE(#VALUE!,0,M27)
M33M33=XERROR.TYPE(#REF!,0,M27)
M34M34=XERROR.TYPE(#NAME?,0,M27)
M35M35=XERROR.TYPE(#NUM!,0,M27)
M36M36=XERROR.TYPE(#N/A,0,M27)
M37M37=XERROR.TYPE(#GETTING_DATA,0,M27)
M38M38=XERROR.TYPE(43,0,M27)
M39M39=XERROR.TYPE("book",0,M27)
M40M40=XERROR.TYPE(TRUE,0,M27)
M41M41=XERROR.TYPE(FALSE,0,M27)
M42M42=XERROR.TYPE("",0,M27)
E47E47=IF(1,"")
C46C46=XERROR.TYPE()
C48C48=XERROR.TYPE(B44)
G46:G49G46=XERROR.TYPE(E46)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top