Need help with "data types" and "conditions" to complete this new LAMBDA I'm working on; thank you!

[Rnkhch]

Hello,

I'm working on a new LAMBDA, called TC (type-based conditions), that checks the data type (specified in two small tables at top of the attached XL2BB), specified in the first parameter, and ensures that the conditions, specified in the third parameter are applied only to the cells that have the specified type. All other cells will return FALSE/0 as the condition(s) will be n/a for them.

TC has the following format:
TC(type,reference,[conditions],[output_style])

If output_style is 0 or omitted, it returns TRUE/FALSE, and if it is 1, it returns 1/0.

The reason I got into this was situations like A1:D1>29 which returns TRUE/1 even when the range contains text values such as "book" (which is the main scenario I'm trying to solve through this LAMBDA). In another scenario, ISBLANK recognizes only real blanks, but not formula blanks, and in another scenario, ISTEXT returns TRUE/1 for formula blanks, etc.

So to avoid any confusions/mistakes, I developed this TC LAMBDA function, that appears to be working well with the exception of two scenarios that I have highlighted in red in the attached XL2BB. So far I haven't been able to figure out the right code for these two scenarios:

U17: =TC(2,$E$2:$K$6,$E$2:$K$6>10,1)
here, the cells are evaluated to be greater than 10, but the specified type is 2 for text, so all cells should appear as FALSE/0.

U45: =TC(31,$E$2:$K$6,$E$2:$K$6=FALSE,1)
here, the cells are evaluated to be FALSE, but the real blank cells gets treated as a zero/FALSE and appears as TRUE/1.

For each data type, I have included a no criteria, and a correct criteria table. I have also included two incorrect criteria meaning that the criteria are n/a relative to the specified type.

I would appreciate any input on this Please let me know if you need further clarification/explnantion on the LAMBDA and how it works.

tc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1		TC	ETS
2		#CALC!	#CALC!		90	87	TRUE	88	1	53	56			1	number		31	real blank
3					43	#DIV/0!	13	15	89		87			2	text		32	formula blank
4		T1F0	IO		69	86	11	#N/A	20	-31	91			3	blank		41	TRUE logical
5		#CALC!	#CALC!			90	0	21	48	48	notebook			4	logical		42	FALSE logical
6					39	15	book	40	FALSE	74	89			16	error		160-1614	specific errors
7		PO0
8		#CALC!
9				data type	no criteria								correct criteria								incorrect criteria								incorrect criteria
10				1	1	1	0	1	1	1	1		1	1	0	1	0	1	1		0	0	0	0	0	0	0		0	0	0	0	0	0	0
11					1	0	1	1	1	0	1		1	0	0	0	1	0	1		0	0	0	0	0	0	0		0	0	0	0	0	0	0
12					1	1	1	0	1	1	1		1	1	0	0	0	0	1		0	0	0	0	0	0	0		0	0	0	0	0	0	0
13					0	1	1	1	1	1	0		0	1	0	0	1	1	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
14					1	1	0	1	0	1	1		0	0	0	0	0	1	1		0	0	0	0	0	0	0		0	0	0	0	0	0	0
15
16					no criteria								correct criteria								incorrect criteria								incorrect criteria
17				2	0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
18					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
19					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
20					0	0	0	0	0	0	1		0	0	0	0	0	0	0		0	0	0	0	0	0	1		0	0	0	0	0	0	0
21					0	0	1	0	0	0	0		0	0	1	0	0	0	0		0	0	1	0	0	0	0		0	0	0	0	0	0	0
22
23					no criteria								correct criteria								incorrect criteria								incorrect criteria
24				3	0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
25					0	0	0	0	0	1	0		0	0	0	0	0	1	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
26					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
27					1	0	0	0	0	0	0		1	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
28					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
29
30					no criteria								correct criteria								incorrect criteria								incorrect criteria
31				4	0	0	1	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
32					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
33					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	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	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
35					0	0	0	0	1	0	0		0	0	0	0	1	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
36
37					no criteria								correct criteria								incorrect criteria								incorrect criteria
38				16	0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
39					0	1	0	0	0	0	0		0	1	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
40					0	0	0	1	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
41					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	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	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
43
44					no criteria								correct criteria								incorrect criteria								incorrect criteria
45				31	0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	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	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
47					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
48					1	0	0	0	0	0	0		1	0	0	0	0	0	0		1	0	0	0	0	0	0		0	0	0	0	0	0	0
49					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
50
51					no criteria								correct criteria								incorrect criteria								incorrect criteria
52				32	0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
53					0	0	0	0	0	1	0		0	0	0	0	0	1	0		0	0	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	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
55					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	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	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
57
58					no criteria								correct criteria								incorrect criteria								incorrect criteria
59				41	0	0	1	0	0	0	0		0	0	1	0	0	0	0		0	0	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	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
61					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	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	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
63					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
64
65					no criteria								correct criteria								incorrect criteria								incorrect criteria
66				42	0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
67					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
68					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
69					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
70					0	0	0	0	1	0	0		0	0	0	0	1	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
71
72					no criteria								correct criteria								incorrect criteria								incorrect criteria
73				162	0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
74					0	1	0	0	0	0	0		0	1	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
75					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
76					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
77					0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0		0	0	0	0	0	0	0
78
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(type,reference,[conditions],[output_style],LET(o,PO0(output_style),c,IF(IO(conditions),1,IFERROR(conditions,0)),e,IF(type<160,(SWITCH(type,1,T1F0(ISNUMBER(reference)),2,LET(r,IFERROR(reference,""),T1F0((ISTEXT(r))*(NOT(r="")))),3,T1F0(IFERROR(IF(reference="",1),0)),4,T1F0(ISLOGICAL(reference)),16,T1F0(ISERROR(reference)),31,T1F0(IFERROR(ISBLANK(reference),0)),32,T1F0(IFERROR((NOT(ISBLANK(reference)))*(reference=""),0)),41,T1F0(IF(ISLOGICAL(reference),reference=TRUE)),42,T1F0(T1F0(IF(ISLOGICAL(reference),reference=FALSE)))))*(c),IF(NOT(ISERROR(MATCH(type,ErrorTypes,0))),T1F0(IFERROR(ERROR.TYPE(reference)=ETS(type),0))*(c))),SWITCH(o,0,IF(e,TRUE,FALSE),1,IF(e,1,0))))
C2	C2	=LAMBDA(type,SWITCH(type,160,0,161,1,162,2,163,3,164,4,165,5,166,6,167,7,168,8,169,9,1610,10,1611,11,1612,12,1613,13,1614,14))
F3	F3	=1/0
J3	J3	=IF(1,"")
H4	H4	=NA()
B5	B5	=LAMBDA(expression,IF(expression,1,0))
C5	C5	=LAMBDA(parameter,ISOMITTED(parameter))
B8	B8	=LAMBDA(parameter,IF(IO(parameter),0,parameter))
E10:K14	E10	=TC(1,$E$2:$K$6,,1)
M10:S14	M10	=TC(1,$E$2:$K$6,$E$2:$K$6>40,1)
U10:AA14	U10	=TC(1,$E$2:$K$6,$E$2:$K$6="book",1)
AC10:AI14	AC10	=TC(1,$E$2:$K$6,$E$2:$K$6="",1)
E17:K21	E17	=TC(2,$E$2:$K$6,,1)
M17:S21	M17	=TC(2,$E$2:$K$6,$E$2:$K$6="book",1)
U17:AA21	U17	=TC(2,$E$2:$K$6,$E$2:$K$6>10,1)
AC17:AI21	AC17	=TC(2,$E$2:$K$6,$E$2:$K$6="",1)
E24:K28	E24	=TC(3,$E$2:$K$6,,1)
M24:S28	M24	=TC(3,$E$2:$K$6,$E$2:$K$6="",1)
U24:AA28	U24	=TC(3,$E$2:$K$6,$E$2:$K$6=3,1)
AC24:AI28	AC24	=TC(3,$E$2:$K$6,$E$2:$K$6="g",1)
E31:K35	E31	=TC(4,$E$2:$K$6,,1)
M31:S35	M31	=TC(4,$E$2:$K$6,$E$2:$K$6=FALSE,1)
U31:AA35	U31	=TC(4,$E$2:$K$6,$E$2:$K$6="",1)
AC31:AI35	AC31	=TC(4,$E$2:$K$6,$E$2:$K$6=0,1)
E38:K42	E38	=TC(16,$E$2:$K$6,,1)
M38:S42	M38	=TC(16,$E$2:$K$6,IF(ISERROR($E$2:$K$6),ERROR.TYPE($E$2:$K$6)=2),1)
U38:AA42	U38	=TC(16,$E$2:$K$6,$E$2:$K$6=12,1)
AC38:AI42	AC38	=TC(16,$E$2:$K$6,$E$2:$K$6=FALSE,1)
E45:K49	E45	=TC(31,$E$2:$K$6,,1)
M45:S49	M45	=TC(31,$E$2:$K$6,$E$2:$K$6="",1)
U45:AA49	U45	=TC(31,$E$2:$K$6,$E$2:$K$6=FALSE,1)
AC45:AI49	AC45	=TC(31,$E$2:$K$6,$E$2:$K$6="book",1)
E52:K56	E52	=TC(32,$E$2:$K$6,,1)
M52:S56	M52	=TC(32,$E$2:$K$6,$E$2:$K$6="",1)
U52:AA56	U52	=TC(32,$E$2:$K$6,$E$2:$K$6="ook",1)
AC52:AI56	AC52	=TC(32,$E$2:$K$6,$E$2:$K$6=TRUE,1)
E59:K63	E59	=TC(41,$E$2:$K$6,,1)
M59:S63	M59	=TC(41,$E$2:$K$6,$E$2:$K$6=TRUE,1)
U59:AA63	U59	=TC(41,$E$2:$K$6,$E$2:$K$6=0,1)
AC59:AI63	AC59	=TC(41,$E$2:$K$6,$E$2:$K$6=1,1)
E66:K70	E66	=TC(42,$E$2:$K$6,,1)
M66:S70	M66	=TC(42,$E$2:$K$6,$E$2:$K$6=FALSE,1)
U66:AA70	U66	=TC(42,$E$2:$K$6,$E$2:$K$6=0,1)
AC66:AI70	AC66	=TC(42,$E$2:$K$6,$E$2:$K$6=1,1)
E73:K77	E73	=TC(162,$E$2:$K$6,,1)
M73:S77	M73	=TC(162,$E$2:$K$6,ERROR.TYPE($E$2:$K$6)=2,1)
U73:AA77	U73	=TC(162,$E$2:$K$6,ERROR.TYPE($E$2:$K$6)=4,1)
AC73:AI77	AC73	=TC(162,$E$2:$K$6,$E$2:$K$6=0,1)
Dynamic array formulas.

 

[Rnkhch]

P.S.

The "ErrorTypes" name in the code refers to: {160,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614} which correspond to the Excel errors 0-14, respectively.