ISXTYPE

[Rnkhch]

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,

=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		#CALC!														basic types
3			a	n	t	5	3	g	6	TRUE	#DIV/0!		t	?		1	dt:NUMBER
4			7.9	#N/A	#SPILL!	FALSE	h		89	book	8-Jul	8	i	TRUE		2	dt:TEXT
5																3	dt:BLANK
6		output:	1	(0 [or omitted] or 1)												4	dt:LOGICAL
7																16	dt:ERROR
8		basic types
9		numbers:	0	0	0	1	1	0	1	0	0	0	0	0		advanced types
10			1	0	0	0	0	0	1	0	1	1	0	0		1	dt:NUMBER
11		texts:	1	1	1	0	0	1	0	0	0	0	1	1		2	dt:TEXT
12			0	0	0	0	1	0	0	1	0	0	1	0		31	dt:BLANK_REAL
13		blanks:	0	0	0	0	0	0	0	0	0	1	0	0		32	dt:BLANK_FORMULA
14			0	0	0	0	0	1	0	0	0	0	0	0		41	dt:LOGICAL_TRUE
15		logicals:	0	0	0	0	0	0	0	1	0	0	0	0		42	dt:LOGICAL_FALSE
16			0	0	0	1	0	0	0	0	0	0	0	1		160	dt:ERROR_EXTERNAL!
17		errors:	0	0	0	0	0	0	0	0	1	0	0	0		161	dt:ERROR_NULL!
18			0	1	1	0	0	0	0	0	0	0	0	0		162	dt:ERROR_DIV/0!
19		wrong type (parameter):														163	dt:ERROR_VALUE!
20																164	dt:ERROR_REF!
21			0	0	0	0	0	0	0	0	0	0	0	0		165	dt:ERROR_NAME?
22		advanced types	0	0	0	0	0	0	0	0	0	0	0	0		166	dt:ERROR_NUM!
23		numbers:	0	0	0	1	1	0	1	0	0	0	0	0		167	dt:ERROR_N/A!
24			1	0	0	0	0	0	1	0	1	1	0	0		168	dt:ERROR_GETTING_DATA!
25		texts:	1	1	1	0	0	1	0	0	0	0	1	1		169	dt:ERROR_SPILL!
26			0	0	0	0	1	0	0	1	0	0	1	0		1610	dt:ERROR_CONNECT!
27		real blanks:	0	0	0	0	0	0	0	0	0	1	0	0		1611	dt:ERROR_BLOCKED!
28			0	0	0	0	0	0	0	0	0	0	0	0		1612	dt:ERROR_UNKNOWN!
29		formula blanks:	0	0	0	0	0	0	0	0	0	0	0	0		1613	dt:ERROR_FIELD!
30			0	0	0	0	0	1	0	0	0	0	0	0		1614	dt:ERROR_CALC!
31		TRUE logicals:	0	0	0	0	0	0	0	1	0	0	0	0
32			0	0	0	0	0	0	0	0	0	0	0	1
33		FALSE logicals:	0	0	0	0	0	0	0	1	0	0	0	0
34			0	0	0	0	0	0	0	0	0	0	0	1
35		error 0	0	0	0	0	0	0	0	0	0	0	0	0
36			0	0	0	0	0	0	0	0	0	0	0	0
37		error 1	0	0	0	0	0	0	0	0	0	0	0	0
38			0	0	0	0	0	0	0	0	0	0	0	0
39		error 2	0	0	0	0	0	0	0	0	1	0	0	0
40			0	0	0	0	0	0	0	0	0	0	0	0
41		error 3	0	0	0	0	0	0	0	0	0	0	0	0
42			0	0	0	0	0	0	0	0	0	0	0	0
43		error 4	0	0	0	0	0	0	0	0	0	0	0	0
44			0	0	0	0	0	0	0	0	0	0	0	0
45		error 5	0	0	0	0	0	0	0	0	0	0	0	0
46			0	0	0	0	0	0	0	0	0	0	0	0
47		error 6	0	0	0	0	0	0	0	0	0	0	0	0
48			0	0	0	0	0	0	0	0	0	0	0	0
49		error 7	0	0	0	0	0	0	0	0	0	0	0	0
50			0	1	0	0	0	0	0	0	0	0	0	0
51		error 8	0	0	0	0	0	0	0	0	0	0	0	0
52			0	0	0	0	0	0	0	0	0	0	0	0
53		error 9	0	0	0	0	0	0	0	0	0	0	0	0
54			0	0	1	0	0	0	0	0	0	0	0	0
55		error 10	0	0	0	0	0	0	0	0	0	0	0	0
56			0	0	0	0	0	0	0	0	0	0	0	0
57		error 11	0	0	0	0	0	0	0	0	0	0	0	0
58			0	0	0	0	0	0	0	0	0	0	0	0
59		error 11	0	0	0	0	0	0	0	0	0	0	0	0
60			0	0	0	0	0	0	0	0	0	0	0	0
61		error 12	0	0	0	0	0	0	0	0	0	0	0	0
62			0	0	0	0	0	0	0	0	0	0	0	0
63		error 13	0	0	0	0	0	0	0	0	0	0	0	0
64			0	0	0	0	0	0	0	0	0	0	0	0
65		error 14	0	0	0	0	0	0	0	0	0	0	0	0
66			0	0	0	0	0	0	0	0	0	0	0	0
67		wrong type (parameter):	#N/A
68
69
70		match criteria
71		numbers >6	0	0	0	0	0	0	0	0	0	0	0	0
72			1	0	0	0	0	0	1	0	1	1	0	0
73		texts = "book"	0	0	0	0	0	0	0	0	0	0	0	0
74			0	0	0	0	0	0	0	1	0	0	0	0
75		blanks:	0	0	0	0	0	0	0	0	0	1	0	0
76			0	0	0	0	0	1	0	0	0	0	0	0
77		logicals:	0	0	0	0	0	0	0	1	0	0	0	0
78			0	0	0	0	0	0	0	0	0	0	0	1
79		numbers >3 and <12	0	0	0	1	0	0	1	0	0	0	0	0
80			1	0	0	0	0	0	0	0	0	1	0	0
81
82		value if true or false
83		both texts	it is false	it is false	it is false	it is true	it is true	it is false	it is true	it is false	it is false	it is false	it is false	it is false
84			it is true	it is false	it is false	it is false	it is false	it is false	it is true	it is false	it is true	it is true	it is false	it is false
85		one text one number	it is true	it is true	it is true	100	100	it is true	100	100	100	100	it is true	it is true
86			100	100	100	100	it is true	100	100	it is true	100	100	it is true	100
87		one text one number	it is false	it is false	it is false	0	0	it is false	0	it is false	it is false	it is false	it is false	it is false
88			0	it is false	it is false	it is false	it is false	it is false	0	it is false	0	0	it is false	it is false
89		both numbers	55	55	55	100	100	55	100	100	100	100	55	55
90			100	100	100	100	55	100	100	55	100	100	55	100
91		one blank one number				100	100		100	100	100	100
92			100	100	100	100		100	100		100	100		100
93		one blank one number	55	55	55			55					55	55
94							55			55			55
95
Sheet7

Cell Formulas
Range	Formula
B2	B2	=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)))))))
K3	K3	=1/0
D4	D4	=NA()
E4	E4	={1,2,3}
H4	H4	=IF(D2>2,1,"")
C9:N10	C9	=ISXTYPE($C$3:$N$4,1,,,$C$6)
C11:N12	C11	=ISXTYPE($C$3:$N$4,2,,,$C$6)
C13:N14	C13	=ISXTYPE($C$3:$N$4,3,,,$C$6)
C15:N16	C15	=ISXTYPE($C$3:$N$4,4,,,$C$6)
C17:N18	C17	=ISXTYPE($C$3:$N$4,16,,,$C$6)
C21:N22	C21	=ISXTYPE($C$3:$N$4,1,,$C$3:$N$4=B8,$C$6)
C23:N24	C23	=ISXTYPE($C$3:$N$4,,1,,$C$6)
C25:N26	C25	=ISXTYPE($C$3:$N$4,,2,,$C$6)
C27:N28	C27	=ISXTYPE($C$3:$N$4,,31,,$C$6)
C29:N30	C29	=ISXTYPE($C$3:$N$4,,32,,$C$6)
C31:N34	C31	=ISXTYPE($C$3:$N$4,,41,,$C$6)
C35:N36	C35	=ISXTYPE($C$3:$N$4,,160,,$C$6)
C37:N38	C37	=ISXTYPE($C$3:$N$4,,161,,$C$6)
C39:N40	C39	=ISXTYPE($C$3:$N$4,,162,,$C$6)
C41:N42	C41	=ISXTYPE($C$3:$N$4,,163,,$C$6)
C43:N44	C43	=ISXTYPE($C$3:$N$4,,164,,$C$6)
C45:N46	C45	=ISXTYPE($C$3:$N$4,,165,,$C$6)
C47:N48	C47	=ISXTYPE($C$3:$N$4,,166,,$C$6)
C49:N50	C49	=ISXTYPE($C$3:$N$4,,167,,$C$6)
C51:N52	C51	=ISXTYPE($C$3:$N$4,,168,,$C$6)
C53:N54	C53	=ISXTYPE($C$3:$N$4,,169,,$C$6)
C55:N56	C55	=ISXTYPE($C$3:$N$4,,1610,,$C$6)
C57:N58	C57	=ISXTYPE($C$3:$N$4,,1611,,$C$6)
C59:N60	C59	=ISXTYPE($C$3:$N$4,,1612,,$C$6)
C61:N64	C61	=ISXTYPE($C$3:$N$4,,1613,,$C$6)
C65:N66	C65	=ISXTYPE($C$3:$N$4,,1614,,$C$6)
C67	C67	=ISXTYPE($C$3:$N$4,,57,,$C$6)
C71:N72	C71	=ISXTYPE($C$3:$N$4,1,,$C$3:$N$4>6,$C$6)
C73:N74	C73	=ISXTYPE($C$3:$N$4,2,,$C$3:$N$4="book",$C$6)
C75:N76	C75	=ISXTYPE($C$3:$N$4,3,,$C$3:$N$4="",$C$6)
C77:N78	C77	=ISXTYPE($C$3:$N$4,4,,$C$3:$N$4=TRUE,$C$6)
C79:N80	C79	=ISXTYPE($C$3:$N$4,1,,($C$3:$N$4>3)*($C$3:$N$4<12),$C$6)
C83:N84	C83	=ISXTYPE($C$3:$N$4,1,,,$C$6,"it is true","it is false")
C85:N86	C85	=ISXTYPE($C$3:$N$4,2,,,$C$6,"it is true",100)
C87:N88	C87	=ISXTYPE($C$3:$N$4,1,,,$C$6,,"it is false")
C89:N90	C89	=ISXTYPE($C$3:$N$4,2,,,$C$6,55,100)
C91:N92	C91	=ISXTYPE($C$3:$N$4,2,,,$C$6,"",100)
C93:N94	C93	=ISXTYPE($C$3:$N$4,2,,,$C$6,55,"")
Dynamic array formulas.

Please feel free to share any thoughts or suggestions.

 

[Rnkhch]

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:

=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

=LAMBDA(parameter,ISOMITTED(parameter))

NIO

=LAMBDA(parameter,NOT(ISOMITTED(parameter)))

BasicTypes

={1,2,3,4,16}

AdvancedTypes

={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.

 

[Rnkhch]

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

=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)

=LAMBDA(parameter,ISOMITTED(parameter))

JAO (Helper function: Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])

=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])

=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 "{}"]))

=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)

=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)

=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)

=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)

=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 "{}"])

=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)

=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])

=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))

AllTypes

={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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		#CALC!														basic types
3			a	n	t	5	3	g	6	TRUE	#DIV/0!		t	?		1	dt:NUMBER
4			7.9	#N/A	#SPILL!	FALSE	h		89	book	8-Jul	8	i	TRUE		2	dt:TEXT
5																3	dt:BLANK
6		output:	1	(0 [or omitted or FALSE] or 1 [or TRUE or any other number besides 0])												4	dt:LOGICAL
7																16	dt:ERROR
8		Individual types
9		1	0	0	0	1	1	0	1	0	0	0	0	0		advanced types
10			1	0	0	0	0	0	1	0	1	1	0	0		1	dt:NUMBER
11		2	1	1	1	0	0	1	0	0	0	0	1	1		2	dt:TEXT
12			0	0	0	0	1	0	0	1	0	0	1	0		31	dt:BLANK_REAL
13		3	0	0	0	0	0	0	0	0	0	1	0	0		32	dt:BLANK_FORMULA
14			0	0	0	0	0	1	0	0	0	0	0	0		41	dt:LOGICAL_TRUE
15		4	0	0	0	0	0	0	0	1	0	0	0	0		42	dt:LOGICAL_FALSE
16			0	0	0	1	0	0	0	0	0	0	0	1		160	dt:ERROR_EXTERNAL!
17		16	0	0	0	0	0	0	0	0	1	0	0	0		161	dt:ERROR_NULL!
18			0	1	1	0	0	0	0	0	0	0	0	0		162	dt:ERROR_DIV/0!
19		31	0	0	0	0	0	0	0	0	0	1	0	0		163	dt:ERROR_VALUE!
20			0	0	0	0	0	0	0	0	0	0	0	0		164	dt:ERROR_REF!
21		32	0	0	0	0	0	0	0	0	0	0	0	0		165	dt:ERROR_NAME?
22			0	0	0	0	0	1	0	0	0	0	0	0		166	dt:ERROR_NUM!
23		41	0	0	0	0	0	0	0	1	0	0	0	0		167	dt:ERROR_N/A!
24			0	0	0	0	0	0	0	0	0	0	0	1		168	dt:ERROR_GETTING_DATA!
25		42	0	0	0	0	0	0	0	1	0	0	0	0		169	dt:ERROR_SPILL!
26			0	0	0	0	0	0	0	0	0	0	0	1		1610	dt:ERROR_CONNECT!
27		160	0	0	0	0	0	0	0	0	0	0	0	0		1611	dt:ERROR_BLOCKED!
28			0	0	0	0	0	0	0	0	0	0	0	0		1612	dt:ERROR_UNKNOWN!
29		161	0	0	0	0	0	0	0	0	0	0	0	0		1613	dt:ERROR_FIELD!
30			0	0	0	0	0	0	0	0	0	0	0	0		1614	dt:ERROR_CALC!
31		162	0	0	0	0	0	0	0	0	1	0	0	0
32			0	0	0	0	0	0	0	0	0	0	0	0
33		163	0	0	0	0	0	0	0	0	0	0	0	0
34			0	0	0	0	0	0	0	0	0	0	0	0
35		164	0	0	0	0	0	0	0	0	0	0	0	0
36			0	0	0	0	0	0	0	0	0	0	0	0
37		165	0	0	0	0	0	0	0	0	0	0	0	0
38			0	0	0	0	0	0	0	0	0	0	0	0
39		166	0	0	0	0	0	0	0	0	0	0	0	0
40			0	0	0	0	0	0	0	0	0	0	0	0
41		167	0	0	0	0	0	0	0	0	0	0	0	0
42			0	1	0	0	0	0	0	0	0	0	0	0
43		168	0	0	0	0	0	0	0	0	0	0	0	0
44			0	0	0	0	0	0	0	0	0	0	0	0
45		169	0	0	0	0	0	0	0	0	0	0	0	0
46			0	0	1	0	0	0	0	0	0	0	0	0
47		1610	0	0	0	0	0	0	0	0	0	0	0	0
48			0	0	0	0	0	0	0	0	0	0	0	0
49		1611	0	0	0	0	0	0	0	0	0	0	0	0
50			0	0	0	0	0	0	0	0	0	0	0	0
51		1612	0	0	0	0	0	0	0	0	0	0	0	0
52			0	0	0	0	0	0	0	0	0	0	0	0
53		1613	0	0	0	0	0	0	0	0	0	0	0	0
54			0	0	0	0	0	0	0	0	0	0	0	0
55		1614	0	0	0	0	0	0	0	0	0	0	0	0
56			0	0	0	0	0	0	0	0	0	0	0	0
57
individual

Cell Formulas
Range	Formula
B2	B2	=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))))))
K3	K3	=1/0
D4	D4	=NA()
E4	E4	={1,2,3}
H4	H4	=IF(D2>2,1,"")
C9:N10	C9	=ISXTYPE($C$3:$N$4,1,,$C$6)
C11:N12	C11	=ISXTYPE($C$3:$N$4,2,,$C$6)
C13:N14	C13	=ISXTYPE($C$3:$N$4,3,,$C$6)
C15:N16	C15	=ISXTYPE($C$3:$N$4,4,,$C$6)
C17:N18	C17	=ISXTYPE($C$3:$N$4,16,,$C$6)
C19:N20	C19	=ISXTYPE($C$3:$N$4,31,,$C$6)
C21:N22	C21	=ISXTYPE($C$3:$N$4,32,,$C$6)
C23:N26	C23	=ISXTYPE($C$3:$N$4,41,,$C$6)
C27:N28	C27	=ISXTYPE($C$3:$N$4,160,,$C$6)
C29:N30	C29	=ISXTYPE($C$3:$N$4,161,,$C$6)
C31:N32	C31	=ISXTYPE($C$3:$N$4,162,,$C$6)
C33:N34	C33	=ISXTYPE($C$3:$N$4,163,,$C$6)
C35:N36	C35	=ISXTYPE($C$3:$N$4,164,,$C$6)
C37:N38	C37	=ISXTYPE($C$3:$N$4,165,,$C$6)
C39:N40	C39	=ISXTYPE($C$3:$N$4,166,,$C$6)
C41:N42	C41	=ISXTYPE($C$3:$N$4,167,,$C$6)
C43:N44	C43	=ISXTYPE($C$3:$N$4,168,,$C$6)
C45:N46	C45	=ISXTYPE($C$3:$N$4,169,,$C$6)
C47:N48	C47	=ISXTYPE($C$3:$N$4,1610,,$C$6)
C49:N50	C49	=ISXTYPE($C$3:$N$4,1611,,$C$6)
C51:N52	C51	=ISXTYPE($C$3:$N$4,1612,,$C$6)
C53:N54	C53	=ISXTYPE($C$3:$N$4,1613,,$C$6)
C55:N56	C55	=ISXTYPE($C$3:$N$4,1614,,$C$6)
Dynamic array formulas.

Multiple types:

ISXTYPE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		#CALC!														basic types
3			a	n	t	5	3	g	6	TRUE	#DIV/0!		t	?		1	dt:NUMBER
4			7.9	#N/A	#SPILL!	FALSE	h		89	book	8-Jul	8	i	TRUE		2	dt:TEXT
5																3	dt:BLANK
6		output:	1	(0 [or omitted or FALSE] or 1 [or TRUE or any other number besides 0])												4	dt:LOGICAL
7																16	dt:ERROR
8		multiple types
9		{1,2}	1	1	1	1	1	1	1	0	0	0	1	1		advanced types
10			1	0	0	0	1	0	1	1	1	1	1	0		1	dt:NUMBER
11		{1,3}	0	0	0	1	1	0	1	0	0	1	0	0		2	dt:TEXT
12			1	0	0	0	0	1	1	0	1	1	0	0		31	dt:BLANK_REAL
13		{1,4}	0	0	0	1	1	0	1	1	0	0	0	0		32	dt:BLANK_FORMULA
14			1	0	0	1	0	0	1	0	1	1	0	1		41	dt:LOGICAL_TRUE
15		{1,16}	0	0	0	1	1	0	1	0	1	0	0	0		42	dt:LOGICAL_FALSE
16			1	1	1	0	0	0	1	0	1	1	0	0		160	dt:ERROR_EXTERNAL!
17		{1,31}	0	0	0	1	1	0	1	0	0	1	0	0		161	dt:ERROR_NULL!
18			1	0	0	0	0	0	1	0	1	1	0	0		162	dt:ERROR_DIV/0!
19		{1,32}	0	0	0	1	1	0	1	0	0	0	0	0		163	dt:ERROR_VALUE!
20			1	0	0	0	0	1	1	0	1	1	0	0		164	dt:ERROR_REF!
21		{1,41}	0	0	0	1	1	0	1	1	0	0	0	0		165	dt:ERROR_NAME?
22			1	0	0	0	0	0	1	0	1	1	0	1		166	dt:ERROR_NUM!
23		{1,42}	0	0	0	1	1	0	1	0	0	0	0	0		167	dt:ERROR_N/A!
24			1	0	0	1	0	0	1	0	1	1	0	0		168	dt:ERROR_GETTING_DATA!
25		{1,162}	0	0	0	1	1	0	1	0	1	0	0	0		169	dt:ERROR_SPILL!
26			1	0	0	0	0	0	1	0	1	1	0	0		1610	dt:ERROR_CONNECT!
27		{2,3}	1	1	1	0	0	1	0	0	0	1	1	1		1611	dt:ERROR_BLOCKED!
28			0	0	0	0	1	1	0	1	0	0	1	0		1612	dt:ERROR_UNKNOWN!
29		{2,4}	1	1	1	0	0	1	0	1	0	0	1	1		1613	dt:ERROR_FIELD!
30			0	0	0	1	1	0	0	1	0	0	1	1		1614	dt:ERROR_CALC!
31		{2,16}	1	1	1	0	0	1	0	0	1	0	1	1
32			0	1	1	0	1	0	0	1	0	0	1	0
33		{2,31}	1	1	1	0	0	1	0	0	0	1	1	1
34			0	0	0	0	1	0	0	1	0	0	1	0
35		{2,32}	1	1	1	0	0	1	0	0	0	0	1	1
36			0	0	0	0	1	1	0	1	0	0	1	0
37		{2,41}	1	1	1	0	0	1	0	1	0	0	1	1
38			0	0	0	0	1	0	0	1	0	0	1	1
39		{2,42}	1	1	1	0	0	1	0	0	0	0	1	1
40			0	0	0	1	1	0	0	1	0	0	1	0
41		{2,167}	1	1	1	0	0	1	0	0	0	0	1	1
42			0	1	0	0	1	0	0	1	0	0	1	0
43		{3,4}	0	0	0	0	0	0	0	1	0	1	0	0
44			0	0	0	1	0	1	0	0	0	0	0	1
45		{3,16}	0	0	0	0	0	0	0	0	1	1	0	0
46			0	1	1	0	0	1	0	0	0	0	0	0
47		{3,31}	0	0	0	0	0	0	0	0	0	1	0	0
48			0	0	0	0	0	1	0	0	0	0	0	0
49		{3,32}	0	0	0	0	0	0	0	0	0	1	0	0
50			0	0	0	0	0	1	0	0	0	0	0	0
51		{3,41}	0	0	0	0	0	0	0	1	0	1	0	0
52			0	0	0	0	0	1	0	0	0	0	0	1
53		{3,42}	0	0	0	0	0	0	0	0	0	1	0	0
54			0	0	0	1	0	1	0	0	0	0	0	0
55		{3,169}	0	0	0	0	0	0	0	0	0	1	0	0
56			0	0	1	0	0	1	0	0	0	0	0	0
57		{4,16}	0	0	0	0	0	0	0	1	1	0	0	0
58			0	1	1	1	0	0	0	0	0	0	0	1
59		{4,31}	0	0	0	0	0	0	0	1	0	1	0	0
60			0	0	0	1	0	0	0	0	0	0	0	1
61		{4,32}	0	0	0	0	0	0	0	1	0	0	0	0
62			0	0	0	1	0	1	0	0	0	0	0	1
63		{4,41}	0	0	0	0	0	0	0	1	0	0	0	0
64			0	0	0	1	0	0	0	0	0	0	0	1
65		{4,42}	0	0	0	0	0	0	0	1	0	0	0	0
66			0	0	0	1	0	0	0	0	0	0	0	1
67		{4,162}	0	0	0	0	0	0	0	1	1	0	0	0
68			0	0	0	1	0	0	0	0	0	0	0	1
69		{16,31}	0	0	0	0	0	0	0	0	1	1	0	0
70			0	1	1	0	0	0	0	0	0	0	0	0
71		{16,32	0	0	0	0	0	0	0	0	1	0	0	0
72			0	1	1	0	0	1	0	0	0	0	0	0
73		{16,41}	0	0	0	0	0	0	0	1	1	0	0	0
74			0	1	1	0	0	0	0	0	0	0	0	1
75		{16,42}	0	0	0	0	0	0	0	0	1	0	0	0
76			0	1	1	1	0	0	0	0	0	0	0	0
77		{16,167}	0	0	0	0	0	0	0	0	1	0	0	0
78			0	1	1	0	0	0	0	0	0	0	0	0
79		{1,2,3}	1	1	1	1	1	1	1	0	0	1	1	1
80			1	0	0	0	1	1	1	1	1	1	1	0
81		{1,2,4}	1	1	1	1	1	1	1	1	0	0	1	1
82			1	0	0	1	1	0	1	1	1	1	1	1
83		{1,2,16}	1	1	1	1	1	1	1	0	1	0	1	1
84			1	1	1	0	1	0	1	1	1	1	1	0
85		{1,2,31}	1	1	1	1	1	1	1	0	0	1	1	1
86			1	0	0	0	1	0	1	1	1	1	1	0
87		{1,2,32}	1	1	1	1	1	1	1	0	0	0	1	1
88			1	0	0	0	1	1	1	1	1	1	1	0
89		{1,2,41}	1	1	1	1	1	1	1	1	0	0	1	1
90			1	0	0	0	1	0	1	1	1	1	1	1
91		{1,2,42}	1	1	1	1	1	1	1	0	0	0	1	1
92			1	0	0	1	1	0	1	1	1	1	1	0
93		{1,2,169}	1	1	1	1	1	1	1	0	0	0	1	1
94			1	0	1	0	1	0	1	1	1	1	1	0
95		{1,3,4}	0	0	0	1	1	0	1	1	0	1	0	0
96			1	0	0	1	0	1	1	0	1	1	0	1
97		{1,3,16}	0	0	0	1	1	0	1	0	1	1	0	0
98			1	1	1	0	0	1	1	0	1	1	0	0
99		{1,3,31}	0	0	0	1	1	0	1	0	0	1	0	0
100			1	0	0	0	0	1	1	0	1	1	0	0
101		{1,3,32}	0	0	0	1	1	0	1	0	0	1	0	0
102			1	0	0	0	0	1	1	0	1	1	0	0
103		{1,3,41}	0	0	0	1	1	0	1	1	0	1	0	0
104			1	0	0	0	0	1	1	0	1	1	0	1
105		{1,3,42}	0	0	0	1	1	0	1	0	0	1	0	0
106			1	0	0	1	0	1	1	0	1	1	0	0
107		{1,3,162}	0	0	0	1	1	0	1	0	1	1	0	0
108			1	0	0	0	0	1	1	0	1	1	0	0
109
multiple

Cell Formulas
Range	Formula
B2	B2	=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))))))
K3	K3	=1/0
D4	D4	=NA()
E4	E4	={1,2,3}
H4	H4	=IF(D2>2,1,"")
C9:N10	C9	=ISXTYPE($C$3:$N$4,{1,2},,$C$6)
C11:N12	C11	=ISXTYPE($C$3:$N$4,{1,3},,$C$6)
C13:N14	C13	=ISXTYPE($C$3:$N$4,{1,4},,$C$6)
C15:N16	C15	=ISXTYPE($C$3:$N$4,{1,16},,$C$6)
C17:N18	C17	=ISXTYPE($C$3:$N$4,{1,31},,$C$6)
C19:N20	C19	=ISXTYPE($C$3:$N$4,{1,32},,$C$6)
C21:N22	C21	=ISXTYPE($C$3:$N$4,{1,41},,$C$6)
C23:N24	C23	=ISXTYPE($C$3:$N$4,{1,42},,$C$6)
C25:N26	C25	=ISXTYPE($C$3:$N$4,{1,162},,$C$6)
C27:N28	C27	=ISXTYPE($C$3:$N$4,{2,3},,$C$6)
C29:N30	C29	=ISXTYPE($C$3:$N$4,{2,4},,$C$6)
C31:N32	C31	=ISXTYPE($C$3:$N$4,{2,16},,$C$6)
C33:N34	C33	=ISXTYPE($C$3:$N$4,{2,31},,$C$6)
C35:N36	C35	=ISXTYPE($C$3:$N$4,{2,32},,$C$6)
C37:N38	C37	=ISXTYPE($C$3:$N$4,{2,41},,$C$6)
C39:N40	C39	=ISXTYPE($C$3:$N$4,{2,42},,$C$6)
C41:N42	C41	=ISXTYPE($C$3:$N$4,{2,167},,$C$6)
C43:N44	C43	=ISXTYPE($C$3:$N$4,{3,4},,$C$6)
C45:N46	C45	=ISXTYPE($C$3:$N$4,{3,16},,$C$6)
C47:N48	C47	=ISXTYPE($C$3:$N$4,{3,31},,$C$6)
C49:N50	C49	=ISXTYPE($C$3:$N$4,{3,32},,$C$6)
C51:N52	C51	=ISXTYPE($C$3:$N$4,{3,41},,$C$6)
C53:N54	C53	=ISXTYPE($C$3:$N$4,{3,42},,$C$6)
C55:N56	C55	=ISXTYPE($C$3:$N$4,{3,169},,$C$6)
C57:N58	C57	=ISXTYPE($C$3:$N$4,{4,16},,$C$6)
C59:N60	C59	=ISXTYPE($C$3:$N$4,{4,31},,$C$6)
C61:N62	C61	=ISXTYPE($C$3:$N$4,{4,32},,$C$6)
C63:N64	C63	=ISXTYPE($C$3:$N$4,{4,41},,$C$6)
C65:N66	C65	=ISXTYPE($C$3:$N$4,{4,42},,$C$6)
C67:N68	C67	=ISXTYPE($C$3:$N$4,{4,162},,$C$6)
C69:N70	C69	=ISXTYPE($C$3:$N$4,{16,31},,$C$6)
C71:N72	C71	=ISXTYPE($C$3:$N$4,{16,32},,$C$6)
C73:N74	C73	=ISXTYPE($C$3:$N$4,{16,41},,$C$6)
C75:N76	C75	=ISXTYPE($C$3:$N$4,{16,42},,$C$6)
C77:N78	C77	=ISXTYPE($C$3:$N$4,{16,167},,$C$6)
C79:N80	C79	=ISXTYPE($C$3:$N$4,{1,2,3},,$C$6)
C81:N82	C81	=ISXTYPE($C$3:$N$4,{1,2,4},,$C$6)
C83:N84	C83	=ISXTYPE($C$3:$N$4,{1,2,16},,$C$6)
C85:N86	C85	=ISXTYPE($C$3:$N$4,{1,2,31},,$C$6)
C87:N88	C87	=ISXTYPE($C$3:$N$4,{1,2,32},,$C$6)
C89:N90	C89	=ISXTYPE($C$3:$N$4,{1,2,41},,$C$6)
C91:N92	C91	=ISXTYPE($C$3:$N$4,{1,2,42},,$C$6)
C93:N94	C93	=ISXTYPE($C$3:$N$4,{1,2,169},,$C$6)
C95:N96	C95	=ISXTYPE($C$3:$N$4,{1,3,4},,$C$6)
C97:N98	C97	=ISXTYPE($C$3:$N$4,{1,3,16},,$C$6)
C99:N100	C99	=ISXTYPE($C$3:$N$4,{1,3,31},,$C$6)
C101:N102	C101	=ISXTYPE($C$3:$N$4,{1,3,32},,$C$6)
C103:N104	C103	=ISXTYPE($C$3:$N$4,{1,3,41},,$C$6)
C105:N106	C105	=ISXTYPE($C$3:$N$4,{1,3,42},,$C$6)
C107:N108	C107	=ISXTYPE($C$3:$N$4,{1,3,162},,$C$6)
Dynamic array formulas.