ISXTYPE

ISXTYPE(reference,[type_basic],[type_advanced],[match_criteria],[output],[value_if_true],[value_if_false])
reference
Required. Specifies the cell/range or array to be analyzed
type_basic
Optional. specifies the basic data types in accordance with the basic data-type output of XTYPE and takes one of five arguments: 1, 2, 3, 4, 16
type_advanced
Optional. specifies the more detailed data types in accordance with the advanced data-type output of XTYPE and takes one of twenty-one arguments: 1, 2, 31, 32, 41, 42, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 1610, 1611, 1612, 1613, 1614
match_criteria
Optional. can be used to include additional matching criteria to further narrow down the output
output
Optional. Specifies the output style and takes one of two arguments: 0 or omitted for TRUE/FALSE style and 1 for 0/1 style
value_if_true
Optional. Specifies the desired output of the function if it evaluates to TRUE (or 1)
value_if_false
Optional. Specifies the desired output of the function if it evaluates to FALSE (or 0)
ISXTYPE, companion to XTYPE, is a powerful alternative to Excel's built-in type-based conditional functions such as ISNUMBER, ISTEXT, etc., while packing other useful features as well
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
528
Office Version
  1. 365
Platform
  1. Windows
ISXTYPE, companion to XTYPE, is a powerful alternative to Excel's built-in type-based conditional functions such as ISNUMBER, ISTEXT, etc., while packing other useful features as well

ISXTYPE was designed to provide conditional output after assessing the precise and high-resolution data type of cells, ranges or elements of arrays, similar to the built-in functions like ISNUMBER, ISTEXT, ISERROR, etc. In addition, matching criteria can be included in an optional parameter to further narrow down the elements under analysis. It also provides optional control over the output through the choice of either 0/1 or TRUE/FALSE. Furthermore, ISXTYPE allows for specific/conditional user-defined outputs to be set through an additional pair of optional parameters. Thus, ISXTYPE may be considered an AGGREGATE-style function that includes the type-based conditional functions.

ISXTYPE consists of one required parameter and six optional parameters as follows:
A) The first parameter, required, specifies the reference to be analyzed.

B) The second parameter, optional, specifies the basic data type intended for the analysis, in accordance with the basic data-type output of XTYPE, and takes the following five arguments:
1 for dt:NUMBER (equivalent to ISNUMBER)
2 for dt:TEXT (equivalent to ISTEXT)
3 for dt:BLANK (equivalent to ISBLANK)
4 for dt:LOGICAL (equivalent to ISLOGICAL)
16 for dt:ERROR (equivalent to ISERROR)

C) The third parameter, optional, specifies the more detailed data type intended for the analysis, in accordance with the advanced data-type output of XTYPE, and takes the following twenty-one arguments:
1 for dt:NUMBER (equivalent to ISNUMBER)
2 for dt:TEXT (equivalent to ISTEXT)
31 for dt:BLANK_REAL
32 for dt:BLANK_FORMULA
41 for dt:LOGICAL_TRUE
42 for dt:LOGICAL_FALSE
160 for dt:ERROR_EXTERNAL!
161 for dt:ERROR_NULL!
162 for dt:ERROR_DIV/0!
163 for dt:ERROR_VALUE!
164 for dt:ERROR_REF!
165 for dt:ERROR_NAME?
166 for dt:ERROR_NUM!
167 for dt:ERROR_N/A
168 for dt:ERROR_GETTING_DATA
169 for dt:ERROR_SPILL!
1610 for dt:ERROR_CONNECT!
1611 for dt:ERROR_BLOCKED!
1612 for dt:ERROR_UNKNOWN!
1613 for dt:ERROR_FIELD!
1614 for dt:ERROR_CALC!

Note that although both the second and third parameters are optional, one (and only one) of them must be specified for the function to work. Thus, specifying both parameters will lead to function returning an error.

D) The fourth parameter, optional, specifies any additional desired match criteria to be included during type analysis to further narrow down the output.

E) The fifth parameter, optional, specifies the output style of the function, with the choice of 0/1 or TRUE/FALSE, and takes one of two arguments as follows:
0 or omitted for TRUE/FALSE output
1 for 0/1 output

F) The sixth parameter, optional, specifies the desired output of the function if it evaluates to TRUE (or 1)

G) The seventh parameter, optional, specifies the desired output of the function if it evaluates to FALSE (or 0)

Note that the sixth or seventh parameters can be used individually or together. If used individually,

Excel Formula: 
=LAMBDA(reference,[type_basic],[type_advanced],[match_criteria],[output],[value_if_true],[value_if_false],
   LET(
      tb,type_basic,
      ta,type_advanced,
      mc,IFERROR(match_criteria,""),
      at,IFS(AND(tb<>"",ta=""),0,AND(tb="",ta<>""),1),
         IF(OR(ISERROR(at),IF(at=0,ISERROR(MATCH(tb,{1,2,3,4,16},0)),ISERROR(MATCH(ta,{1,2,31,32,41,42,160,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614},0))),),
            UNIQUE(NA()),
            LET(
               ref,reference,
               op,output,
               pc,XTYPE(ref,at)=SWITCH(at,0,SWITCH(tb,1,"dt:NUMBER",2,"dt:TEXT",3,"dt:BLANK",16,"dt:ERROR",4,"dt:LOGICAL"),1,SWITCH(ta,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK_REAL",32,"dt:BLANK_FORMULA",41,"dt:LOGICAL_TRUE",42,"dt:LOGICAL_FALSE",160,"dt:ERROR_EXTERNAL!",161,"dt:ERROR_NULL!",162,"dt:ERROR_DIV/0!",163,"dt:ERROR_VALUE!",164,"dt:ERROR_REF!",165,"dt:ERROR_NAME?",166,"dt:ERROR_NUM!",167,"dt:ERROR_N/A",168,"dt:ERROR_GETTING_DATA",169,"dt:ERROR_SPILL!",1610,"dt:ERROR_CONNECT!",1611,"dt:ERROR_BLOCKED!",1612,"dt:ERROR_UNKNOWN!",1613,"dt:ERROR_FIELD!",1614,"dt:ERROR_CALC!")),
               pcm,IF(mc="",pc,mc*pc),
               fop,IF(pcm,IF(OR(op="",op<=0),TRUE,IF(op>=1,1)),IF(OR(op="",op<=0),FALSE,IF(op>=1,0))),
                  LET(
                     vit,value_if_true,
                     vif,value_if_false,
                        IF(AND(ISOMITTED(vit),ISOMITTED(vif)),
                           fop,
                           IF(fop,vit,vif)
                        )
                  )
            )
      )
   )
)

Note that in the XL2BB, I have set the output style (fifth) parameter to be read from a cell ($C$6) so that the output can be easily flipped between 0/1 and TRUE/FALSE:

isxtype.xlsx
ABCDEFGHIJKLMNOPQR
1
2#CALC!basic types
3ant53g6TRUE#DIV/0!t?1dt:NUMBER
47.9#N/A#SPILL!FALSEh 89book8-Jul8iTRUE2dt:TEXT
53dt:BLANK
6output:1(0 [or omitted] or 1)4dt:LOGICAL
716dt:ERROR
8basic types
9numbers:000110100000advanced types
101000001011001dt:NUMBER
11texts:1110010000112dt:TEXT
1200001001001031dt:BLANK_REAL
13blanks:00000000010032dt:BLANK_FORMULA
1400000100000041dt:LOGICAL_TRUE
15logicals:00000001000042dt:LOGICAL_FALSE
16000100000001160dt:ERROR_EXTERNAL!
17errors:000000001000161dt:ERROR_NULL!
18011000000000162dt:ERROR_DIV/0!
19wrong type (parameter):163dt:ERROR_VALUE!
20164dt:ERROR_REF!
21000000000000165dt:ERROR_NAME?
22advanced types000000000000166dt:ERROR_NUM!
23numbers:000110100000167dt:ERROR_N/A!
24100000101100168dt:ERROR_GETTING_DATA!
25texts:111001000011169dt:ERROR_SPILL!
260000100100101610dt:ERROR_CONNECT!
27real blanks:0000000001001611dt:ERROR_BLOCKED!
280000000000001612dt:ERROR_UNKNOWN!
29formula blanks:0000000000001613dt:ERROR_FIELD!
300000010000001614dt:ERROR_CALC!
31TRUE logicals:000000010000
32000000000001
33FALSE logicals:000000010000
34000000000001
35error 0000000000000
36000000000000
37error 1000000000000
38000000000000
39error 2000000001000
40000000000000
41error 3000000000000
42000000000000
43error 4000000000000
44000000000000
45error 5000000000000
46000000000000
47error 6000000000000
48000000000000
49error 7000000000000
50010000000000
51error 8000000000000
52000000000000
53error 9000000000000
54001000000000
55error 10000000000000
56000000000000
57error 11000000000000
58000000000000
59error 11000000000000
60000000000000
61error 12000000000000
62000000000000
63error 13000000000000
64000000000000
65error 14000000000000
66000000000000
67wrong type (parameter):#N/A
68
69
70match criteria
71numbers >6000000000000
72100000101100
73texts = "book"000000000000
74000000010000
75blanks:000000000100
76000001000000
77logicals:000000010000
78000000000001
79numbers >3 and <12000100100000
80100000000100
81
82value if true or false
83both textsit is falseit is falseit is falseit is trueit is trueit is falseit is trueit is falseit is falseit is falseit is falseit is false
84it is trueit is falseit is falseit is falseit is falseit is falseit is trueit is falseit is trueit is trueit is falseit is false
85one text one numberit is trueit is trueit is true100100it is true100100100100it is trueit is true
86100100100100it is true100100it is true100100it is true100
87one text one numberit is falseit is falseit is false00it is false0it is falseit is falseit is falseit is falseit is false
880it is falseit is falseit is falseit is falseit is false0it is false00it is falseit is false
89both numbers555555100100551001001001005555
90100100100100551001005510010055100
91one blank one number 100100100100100100
92100100100100100100100100100
93one blank one number555555555555
94555555
95
Sheet7
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[type_basic],[type_advanced],[match_criteria],[output],[value_if_true],[value_if_false],LET(tb,type_basic,ta,type_advanced,mc,IFERROR(match_criteria,""),at,IFS(AND(tb<>"",ta=""),0,AND(tb="",ta<>""),1),IF(OR(ISERROR(at),IF(at=0,ISERROR(MATCH(tb,{1,2,3,4,16},0)),ISERROR(MATCH(ta,{1,2,31,32,41,42,160,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614},0))),),UNIQUE(NA()),LET(ref,reference,op,output,pc,XTYPE(ref,at)=SWITCH(at,0,SWITCH(tb,1,"dt:NUMBER",2,"dt:TEXT",3,"dt:BLANK",16,"dt:ERROR",4,"dt:LOGICAL"),1,SWITCH(ta,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK_REAL",32,"dt:BLANK_FORMULA",41,"dt:LOGICAL_TRUE",42,"dt:LOGICAL_FALSE",160,"dt:ERROR_EXTERNAL!",161,"dt:ERROR_NULL!",162,"dt:ERROR_DIV/0!",163,"dt:ERROR_VALUE!",164,"dt:ERROR_REF!",165,"dt:ERROR_NAME?",166,"dt:ERROR_NUM!",167,"dt:ERROR_N/A",168,"dt:ERROR_GETTING_DATA",169,"dt:ERROR_SPILL!",1610,"dt:ERROR_CONNECT!",1611,"dt:ERROR_BLOCKED!",1612,"dt:ERROR_UNKNOWN!",1613,"dt:ERROR_FIELD!",1614,"dt:ERROR_CALC!")),pcm,IF(mc="",pc,mc*pc),fop,IF(pcm,IF(OR(op="",op<=0),TRUE,IF(op>=1,1)),IF(OR(op="",op<=0),FALSE,IF(op>=1,0))),LET(vit,value_if_true,vif,value_if_false,IF(AND(ISOMITTED(vit),ISOMITTED(vif)),fop,IF(fop,vit,vif)))))))
K3K3=1/0
D4D4=NA()
E4E4={1,2,3}
H4H4=IF(D2>2,1,"")
C9:N10C9=ISXTYPE($C$3:$N$4,1,,,$C$6)
C11:N12C11=ISXTYPE($C$3:$N$4,2,,,$C$6)
C13:N14C13=ISXTYPE($C$3:$N$4,3,,,$C$6)
C15:N16C15=ISXTYPE($C$3:$N$4,4,,,$C$6)
C17:N18C17=ISXTYPE($C$3:$N$4,16,,,$C$6)
C21:N22C21=ISXTYPE($C$3:$N$4,1,,$C$3:$N$4=B8,$C$6)
C23:N24C23=ISXTYPE($C$3:$N$4,,1,,$C$6)
C25:N26C25=ISXTYPE($C$3:$N$4,,2,,$C$6)
C27:N28C27=ISXTYPE($C$3:$N$4,,31,,$C$6)
C29:N30C29=ISXTYPE($C$3:$N$4,,32,,$C$6)
C31:N34C31=ISXTYPE($C$3:$N$4,,41,,$C$6)
C35:N36C35=ISXTYPE($C$3:$N$4,,160,,$C$6)
C37:N38C37=ISXTYPE($C$3:$N$4,,161,,$C$6)
C39:N40C39=ISXTYPE($C$3:$N$4,,162,,$C$6)
C41:N42C41=ISXTYPE($C$3:$N$4,,163,,$C$6)
C43:N44C43=ISXTYPE($C$3:$N$4,,164,,$C$6)
C45:N46C45=ISXTYPE($C$3:$N$4,,165,,$C$6)
C47:N48C47=ISXTYPE($C$3:$N$4,,166,,$C$6)
C49:N50C49=ISXTYPE($C$3:$N$4,,167,,$C$6)
C51:N52C51=ISXTYPE($C$3:$N$4,,168,,$C$6)
C53:N54C53=ISXTYPE($C$3:$N$4,,169,,$C$6)
C55:N56C55=ISXTYPE($C$3:$N$4,,1610,,$C$6)
C57:N58C57=ISXTYPE($C$3:$N$4,,1611,,$C$6)
C59:N60C59=ISXTYPE($C$3:$N$4,,1612,,$C$6)
C61:N64C61=ISXTYPE($C$3:$N$4,,1613,,$C$6)
C65:N66C65=ISXTYPE($C$3:$N$4,,1614,,$C$6)
C67C67=ISXTYPE($C$3:$N$4,,57,,$C$6)
C71:N72C71=ISXTYPE($C$3:$N$4,1,,$C$3:$N$4>6,$C$6)
C73:N74C73=ISXTYPE($C$3:$N$4,2,,$C$3:$N$4="book",$C$6)
C75:N76C75=ISXTYPE($C$3:$N$4,3,,$C$3:$N$4="",$C$6)
C77:N78C77=ISXTYPE($C$3:$N$4,4,,$C$3:$N$4=TRUE,$C$6)
C79:N80C79=ISXTYPE($C$3:$N$4,1,,($C$3:$N$4>3)*($C$3:$N$4<12),$C$6)
C83:N84C83=ISXTYPE($C$3:$N$4,1,,,$C$6,"it is true","it is false")
C85:N86C85=ISXTYPE($C$3:$N$4,2,,,$C$6,"it is true",100)
C87:N88C87=ISXTYPE($C$3:$N$4,1,,,$C$6,,"it is false")
C89:N90C89=ISXTYPE($C$3:$N$4,2,,,$C$6,55,100)
C91:N92C91=ISXTYPE($C$3:$N$4,2,,,$C$6,"",100)
C93:N94C93=ISXTYPE($C$3:$N$4,2,,,$C$6,55,"")
Dynamic array formulas.



Please feel free to share any thoughts or suggestions.
 
Upvote 0
Here is the updated/optimized code that includes a few defined names in it to keep the code a bit shorter and easier to follow; also included is a bug fix for the value_if_true and value_if_false parameters which fixed the issue that if one of these two parameters where omitted, a "0" was displayed for the other parameter:

Excel Formula: 
=LAMBDA(reference,[type_basic],[type_advanced],[match_criteria],[output],[value_if_true],[value_if_false],
   LET(
      tb,type_basic,
      ta,type_advanced,
      mc,IFERROR(match_criteria,0),
      at,IFS(AND(NIO(type_basic),IO(type_advanced)),2,AND(IO(type_basic),NIO(type_advanced)),3),
         IF(OR(ISERROR(at),IF(at=2,ISERROR(MATCH(tb,BasicTypes,0)),ISERROR(MATCH(ta,AdvancedTypes,0)))),
         UNIQUE(NA()),
            LET(
               ref,reference,
               op,output,
               pc,XTYPE(ref,at)=SWITCH(at,2,tb,3,ta),
               pcm,IF(IO(match_criteria),pc,mc*pc),
               fop,IF(pcm,IF(OR(IO(output),op=0),TRUE,IF(op=1,1)),IF(OR(IO(output),op=0),FALSE,IF(op=1,0))),
                  IF(AND(IO(value_if_true),IO(value_if_false)),
                  fop,
                  IF(fop,IF(IO(value_if_true),fop,value_if_true),IF(IO(value_if_false),fop,value_if_false))
                  )
             )
         )
   )
)

The following names and helper LAMBDAs are used in this code:

XTYPE() which is posted

IO
Excel Formula: 
=LAMBDA(parameter,ISOMITTED(parameter))

NIO
Excel Formula: 
=LAMBDA(parameter,NOT(ISOMITTED(parameter)))

BasicTypes
Excel Formula: 
={1,2,3,4,16}

AdvancedTypes
Excel Formula: 
={1,2,31,32,41,42,160,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614}

Also, the note (regarding value_if_true and value_if_false) after item G in the original post was left unfinished; here is the complete note:

Note that the sixth or seventh parameters can be used individually or together. If used individually, the other parameter will continue to display TRUE/FALSE or 1/0 as specified at the fifth (output) parameter.
 
Updated code:
1) addresses the issue where the function spill is affected by incorrect parameters
2) combines type_basic and type_advanced parameters into a single "type" parameter
3) enables entering more than one type for the "type" parameter through the array brackets "{}". This allows for a remarkable level of flexibility and full resolution for selecting the desired data types, as demonstrated through several examples in the attached XL2BB below. (Note that the number of type permutations are huge; I have only shown a few.)

(You can download my template from the link in my signature below, which makes it easier to test the functions compared to pasting the XL2BB and entering the functions manually)

ISXTYPE
VBA Code: 
=LAMBDA(reference,types,[match_criteria],[output],[value_if_true],[value_if_false],
   IF(
      OR(IO(types),JAO(MATCH(types,AllTypes,0)),JAN(output),JAN(value_if_true),JAN(value_if_false)),
      NA(),
      LET(
         m,IFERROR(match_criteria,0),
         o,PLSL(output),
         c,JAB(types,XTYPE(reference,3)),
         d,IF(IO(match_criteria),c,m*c),
         f,IF(d,OT(o),OF(o)),
         IF(
            AND(IO(value_if_true),IO(value_if_false)),
            f,
            IF(
               f,
               IF(IO(value_if_true),f,value_if_true),
               IF(IO(value_if_false),f,value_if_false)
            )
         )
      )
   )
)

XTYPE (posted separately)

XERROR.TYPE (posted separately)

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

JAO (Helper function: Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
VBA Code: 
=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))

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

PLSL (stands for "parameter limit, single, logical")
(Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either anything not logically evaluateble or more than a single entry in array brackets "{}"]))
VBA Code: 
=LAMBDA([parameter],IF(IO(parameter),0,IF(JAN(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))

JAB (Helper function: returns a number greater than or equal to 1 [i.e. TRUE] if the reference has the specified data type(s) and 0 [i.e. FALSE] otherwise)
VBA Code: 
=LAMBDA(types,advanced_types_array,
   LET(
      basic_types_array,SWITCH(advanced_types_array,1,1,2,2,31,3,32,3,41,4,42,4,16),
      MAP(advanced_types_array,LAMBDA(a,OR(a=types)))+MAP(basic_types_array,LAMBDA(a,OR(a=types)))
   )
)

OF (Returns "FALSE" if the reference is TRUE, "0" if the reference is FALSE, and #N/A otherwise)
VBA Code: 
=LAMBDA(reference,IF(ISERROR(IF(reference="",NA(),reference)),NA(),IFERROR(IF(reference,0,FALSE),NA())))

OT (Returns "TRUE" if the reference is TRUE, "1" if the reference is FALSE, and #N/A otherwise)
VBA Code: 
=LAMBDA(reference,IF(ISERROR(IF(reference="",NA(),reference)),NA(),IFERROR(IF(reference,1,TRUE),NA())))

T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)
VBA Code: 
=LAMBDA(expression,IF(ISNUMBER(expression)+ISLOGICAL(expression),IF(expression,1,0),NA()))

PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either the wrong type or more than a single entry in array brackets "{}"])
VBA Code: 
=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)
VBA Code: 
=LAMBDA([parameter],IF(IO(parameter),0,parameter))

JAP (Helper function: Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
VBA Code: 
=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))

AllTypes
VBA Code: 
={1,2,3,4,16,31,32,41,42,160,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614}

Single types:
ISXTYPE.xlsx
ABCDEFGHIJKLMNOPQR
1
2#CALC!basic types
3ant53g6TRUE#DIV/0!t?1dt:NUMBER
47.9#N/A#SPILL!FALSEh 89book8-Jul8iTRUE2dt:TEXT
53dt:BLANK
6output:1(0 [or omitted or FALSE] or 1 [or TRUE or any other number besides 0])4dt:LOGICAL
716dt:ERROR
8Individual types
91000110100000advanced types
101000001011001dt:NUMBER
1121110010000112dt:TEXT
1200001001001031dt:BLANK_REAL
13300000000010032dt:BLANK_FORMULA
1400000100000041dt:LOGICAL_TRUE
15400000001000042dt:LOGICAL_FALSE
16000100000001160dt:ERROR_EXTERNAL!
1716000000001000161dt:ERROR_NULL!
18011000000000162dt:ERROR_DIV/0!
1931000000000100163dt:ERROR_VALUE!
20000000000000164dt:ERROR_REF!
2132000000000000165dt:ERROR_NAME?
22000001000000166dt:ERROR_NUM!
2341000000010000167dt:ERROR_N/A!
24000000000001168dt:ERROR_GETTING_DATA!
2542000000010000169dt:ERROR_SPILL!
260000000000011610dt:ERROR_CONNECT!
271600000000000001611dt:ERROR_BLOCKED!
280000000000001612dt:ERROR_UNKNOWN!
291610000000000001613dt:ERROR_FIELD!
300000000000001614dt:ERROR_CALC!
31162000000001000
32000000000000
33163000000000000
34000000000000
35164000000000000
36000000000000
37165000000000000
38000000000000
39166000000000000
40000000000000
41167000000000000
42010000000000
43168000000000000
44000000000000
45169000000000000
46001000000000
471610000000000000
48000000000000
491611000000000000
50000000000000
511612000000000000
52000000000000
531613000000000000
54000000000000
551614000000000000
56000000000000
57
individual
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,types,[match_criteria],[output],[value_if_true],[value_if_false],IF(OR(IO(types),JAO(MATCH(types,AllTypes,0)),JAN(output),JAN(value_if_true),JAN(value_if_false)),NA(),LET(m,IFERROR(match_criteria,0),o,PLSL(output),c,JAB(types,XTYPE(reference,3)),d,IF(IO(match_criteria),c,m*c),f,IF(d,OT(o),OF(o)),IF(AND(IO(value_if_true),IO(value_if_false)),f,IF(f,IF(IO(value_if_true),f,value_if_true),IF(IO(value_if_false),f,value_if_false))))))
K3K3=1/0
D4D4=NA()
E4E4={1,2,3}
H4H4=IF(D2>2,1,"")
C9:N10C9=ISXTYPE($C$3:$N$4,1,,$C$6)
C11:N12C11=ISXTYPE($C$3:$N$4,2,,$C$6)
C13:N14C13=ISXTYPE($C$3:$N$4,3,,$C$6)
C15:N16C15=ISXTYPE($C$3:$N$4,4,,$C$6)
C17:N18C17=ISXTYPE($C$3:$N$4,16,,$C$6)
C19:N20C19=ISXTYPE($C$3:$N$4,31,,$C$6)
C21:N22C21=ISXTYPE($C$3:$N$4,32,,$C$6)
C23:N26C23=ISXTYPE($C$3:$N$4,41,,$C$6)
C27:N28C27=ISXTYPE($C$3:$N$4,160,,$C$6)
C29:N30C29=ISXTYPE($C$3:$N$4,161,,$C$6)
C31:N32C31=ISXTYPE($C$3:$N$4,162,,$C$6)
C33:N34C33=ISXTYPE($C$3:$N$4,163,,$C$6)
C35:N36C35=ISXTYPE($C$3:$N$4,164,,$C$6)
C37:N38C37=ISXTYPE($C$3:$N$4,165,,$C$6)
C39:N40C39=ISXTYPE($C$3:$N$4,166,,$C$6)
C41:N42C41=ISXTYPE($C$3:$N$4,167,,$C$6)
C43:N44C43=ISXTYPE($C$3:$N$4,168,,$C$6)
C45:N46C45=ISXTYPE($C$3:$N$4,169,,$C$6)
C47:N48C47=ISXTYPE($C$3:$N$4,1610,,$C$6)
C49:N50C49=ISXTYPE($C$3:$N$4,1611,,$C$6)
C51:N52C51=ISXTYPE($C$3:$N$4,1612,,$C$6)
C53:N54C53=ISXTYPE($C$3:$N$4,1613,,$C$6)
C55:N56C55=ISXTYPE($C$3:$N$4,1614,,$C$6)
Dynamic array formulas.


Multiple types:
ISXTYPE.xlsx
ABCDEFGHIJKLMNOPQR
1
2#CALC!basic types
3ant53g6TRUE#DIV/0!t?1dt:NUMBER
47.9#N/A#SPILL!FALSEh 89book8-Jul8iTRUE2dt:TEXT
53dt:BLANK
6output:1(0 [or omitted or FALSE] or 1 [or TRUE or any other number besides 0])4dt:LOGICAL
716dt:ERROR
8multiple types
9{1,2}111111100011advanced types
101000101111101dt:NUMBER
11{1,3}0001101001002dt:TEXT
1210000110110031dt:BLANK_REAL
13{1,4}00011011000032dt:BLANK_FORMULA
1410010010110141dt:LOGICAL_TRUE
15{1,16}00011010100042dt:LOGICAL_FALSE
16111000101100160dt:ERROR_EXTERNAL!
17{1,31}000110100100161dt:ERROR_NULL!
18100000101100162dt:ERROR_DIV/0!
19{1,32}000110100000163dt:ERROR_VALUE!
20100001101100164dt:ERROR_REF!
21{1,41}000110110000165dt:ERROR_NAME?
22100000101101166dt:ERROR_NUM!
23{1,42}000110100000167dt:ERROR_N/A!
24100100101100168dt:ERROR_GETTING_DATA!
25{1,162}000110101000169dt:ERROR_SPILL!
261000001011001610dt:ERROR_CONNECT!
27{2,3}1110010001111611dt:ERROR_BLOCKED!
280000110100101612dt:ERROR_UNKNOWN!
29{2,4}1110010100111613dt:ERROR_FIELD!
300001100100111614dt:ERROR_CALC!
31{2,16}111001001011
32011010010010
33{2,31}111001000111
34000010010010
35{2,32}111001000011
36000011010010
37{2,41}111001010011
38000010010011
39{2,42}111001000011
40000110010010
41{2,167}111001000011
42010010010010
43{3,4}000000010100
44000101000001
45{3,16}000000001100
46011001000000
47{3,31}000000000100
48000001000000
49{3,32}000000000100
50000001000000
51{3,41}000000010100
52000001000001
53{3,42}000000000100
54000101000000
55{3,169}000000000100
56001001000000
57{4,16}000000011000
58011100000001
59{4,31}000000010100
60000100000001
61{4,32}000000010000
62000101000001
63{4,41}000000010000
64000100000001
65{4,42}000000010000
66000100000001
67{4,162}000000011000
68000100000001
69{16,31}000000001100
70011000000000
71{16,32000000001000
72011001000000
73{16,41}000000011000
74011000000001
75{16,42}000000001000
76011100000000
77{16,167}000000001000
78011000000000
79{1,2,3}111111100111
80100011111110
81{1,2,4}111111110011
82100110111111
83{1,2,16}111111101011
84111010111110
85{1,2,31}111111100111
86100010111110
87{1,2,32}111111100011
88100011111110
89{1,2,41}111111110011
90100010111111
91{1,2,42}111111100011
92100110111110
93{1,2,169}111111100011
94101010111110
95{1,3,4}000110110100
96100101101101
97{1,3,16}000110101100
98111001101100
99{1,3,31}000110100100
100100001101100
101{1,3,32}000110100100
102100001101100
103{1,3,41}000110110100
104100001101101
105{1,3,42}000110100100
106100101101100
107{1,3,162}000110101100
108100001101100
109
multiple
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,types,[match_criteria],[output],[value_if_true],[value_if_false],IF(OR(IO(types),JAO(MATCH(types,AllTypes,0)),JAN(output),JAN(value_if_true),JAN(value_if_false)),NA(),LET(m,IFERROR(match_criteria,0),o,PLSL(output),c,JAB(types,XTYPE(reference,3)),d,IF(IO(match_criteria),c,m*c),f,IF(d,OT(o),OF(o)),IF(AND(IO(value_if_true),IO(value_if_false)),f,IF(f,IF(IO(value_if_true),f,value_if_true),IF(IO(value_if_false),f,value_if_false))))))
K3K3=1/0
D4D4=NA()
E4E4={1,2,3}
H4H4=IF(D2>2,1,"")
C9:N10C9=ISXTYPE($C$3:$N$4,{1,2},,$C$6)
C11:N12C11=ISXTYPE($C$3:$N$4,{1,3},,$C$6)
C13:N14C13=ISXTYPE($C$3:$N$4,{1,4},,$C$6)
C15:N16C15=ISXTYPE($C$3:$N$4,{1,16},,$C$6)
C17:N18C17=ISXTYPE($C$3:$N$4,{1,31},,$C$6)
C19:N20C19=ISXTYPE($C$3:$N$4,{1,32},,$C$6)
C21:N22C21=ISXTYPE($C$3:$N$4,{1,41},,$C$6)
C23:N24C23=ISXTYPE($C$3:$N$4,{1,42},,$C$6)
C25:N26C25=ISXTYPE($C$3:$N$4,{1,162},,$C$6)
C27:N28C27=ISXTYPE($C$3:$N$4,{2,3},,$C$6)
C29:N30C29=ISXTYPE($C$3:$N$4,{2,4},,$C$6)
C31:N32C31=ISXTYPE($C$3:$N$4,{2,16},,$C$6)
C33:N34C33=ISXTYPE($C$3:$N$4,{2,31},,$C$6)
C35:N36C35=ISXTYPE($C$3:$N$4,{2,32},,$C$6)
C37:N38C37=ISXTYPE($C$3:$N$4,{2,41},,$C$6)
C39:N40C39=ISXTYPE($C$3:$N$4,{2,42},,$C$6)
C41:N42C41=ISXTYPE($C$3:$N$4,{2,167},,$C$6)
C43:N44C43=ISXTYPE($C$3:$N$4,{3,4},,$C$6)
C45:N46C45=ISXTYPE($C$3:$N$4,{3,16},,$C$6)
C47:N48C47=ISXTYPE($C$3:$N$4,{3,31},,$C$6)
C49:N50C49=ISXTYPE($C$3:$N$4,{3,32},,$C$6)
C51:N52C51=ISXTYPE($C$3:$N$4,{3,41},,$C$6)
C53:N54C53=ISXTYPE($C$3:$N$4,{3,42},,$C$6)
C55:N56C55=ISXTYPE($C$3:$N$4,{3,169},,$C$6)
C57:N58C57=ISXTYPE($C$3:$N$4,{4,16},,$C$6)
C59:N60C59=ISXTYPE($C$3:$N$4,{4,31},,$C$6)
C61:N62C61=ISXTYPE($C$3:$N$4,{4,32},,$C$6)
C63:N64C63=ISXTYPE($C$3:$N$4,{4,41},,$C$6)
C65:N66C65=ISXTYPE($C$3:$N$4,{4,42},,$C$6)
C67:N68C67=ISXTYPE($C$3:$N$4,{4,162},,$C$6)
C69:N70C69=ISXTYPE($C$3:$N$4,{16,31},,$C$6)
C71:N72C71=ISXTYPE($C$3:$N$4,{16,32},,$C$6)
C73:N74C73=ISXTYPE($C$3:$N$4,{16,41},,$C$6)
C75:N76C75=ISXTYPE($C$3:$N$4,{16,42},,$C$6)
C77:N78C77=ISXTYPE($C$3:$N$4,{16,167},,$C$6)
C79:N80C79=ISXTYPE($C$3:$N$4,{1,2,3},,$C$6)
C81:N82C81=ISXTYPE($C$3:$N$4,{1,2,4},,$C$6)
C83:N84C83=ISXTYPE($C$3:$N$4,{1,2,16},,$C$6)
C85:N86C85=ISXTYPE($C$3:$N$4,{1,2,31},,$C$6)
C87:N88C87=ISXTYPE($C$3:$N$4,{1,2,32},,$C$6)
C89:N90C89=ISXTYPE($C$3:$N$4,{1,2,41},,$C$6)
C91:N92C91=ISXTYPE($C$3:$N$4,{1,2,42},,$C$6)
C93:N94C93=ISXTYPE($C$3:$N$4,{1,2,169},,$C$6)
C95:N96C95=ISXTYPE($C$3:$N$4,{1,3,4},,$C$6)
C97:N98C97=ISXTYPE($C$3:$N$4,{1,3,16},,$C$6)
C99:N100C99=ISXTYPE($C$3:$N$4,{1,3,31},,$C$6)
C101:N102C101=ISXTYPE($C$3:$N$4,{1,3,32},,$C$6)
C103:N104C103=ISXTYPE($C$3:$N$4,{1,3,41},,$C$6)
C105:N106C105=ISXTYPE($C$3:$N$4,{1,3,42},,$C$6)
C107:N108C107=ISXTYPE($C$3:$N$4,{1,3,162},,$C$6)
Dynamic array formulas.
 
Last edited:
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

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