MATCHES

[Rnkhch]

MATCHES is a powerful enhancement to the Excel's built-in MATCH function and returns the relative positions of all items in a one-dimensional array that match a specified value

Excel's built-in MATCH function returns the relative position of the first matching item in a one-dimensional array. MATCHES was designed to overcome this limitation by returning the relative positions of all matching items in a one-dimensional array. In addition, for numeric matches, there are five matching criteria available in MATCHES (i.e., equal, greater than, greater than or equal, less than, and less than or equal) versus the three matching criteria available in MATCH. Also note that MATCHES uses the XTYPE function.

MATCHES consists of two required parameters and four optional parameters as follows:
A) The first parameter, required, specifies the value that is intended to be looked up in an array.

B) The second parameter, required, specifies the one-dimensional array that is used for the lookup.

C) The third parameter, optional, specifies the match type that is considered during lookup and takes the following five arguments:
0 or omitted, for 'equal to'
1 for 'greater than'
2 for 'greater than or equal to'
-1 for 'less than'
-2 for 'less than or equal to'

D) The fourth parameter, optional, is utilized to specially lookup errors and takes the following fifteen arguments:
-1 indicates all errors
0 for #EXTERNAL!
1 for #NULL!
2 for #DIV/0!
3 for #VALUE!
4 for #REF!
5 for #NAME?
6 for #NUM!
7 for #N/A
8 for #GETTING_DATA
9 for #SPILL!
10 for #CONNECT!
11 for #BLOCKED!
12 for #UNKNOWN!
13 for #FIELD!
14 for #CALC!

E) The fifth parameter, optional, is utilized to specifically lookup blanks and takes the following three arguments:
0 for all blanks
1 for real blanks
2 for formula blanks

Note that all blanks can also be looked up by specifying either nothing or "" for lookup_value in which case the 0 argument may or may not be specified.

F) The sixth parameter, optional, is used to specifically lookup logicals and takes the following three arguments:
-1 for all logicals
0 for FALSE
1 for TRUE

Note that TRUE or FALSE values can also be looked up by specifying the lookup_value to be TRUE or FALSE in which case the sixth parameters is not utilized.

=LAMBDA(lookup_value,lookup_1D_array,[match_type],[errors],[blanks],[logicals],
   LET(exp,
      LET(
         lv,lookup_value,
         a,lookup_1D_array,
         c,COLUMNS(a),
         r,ROWS(a),
         hv,IF(c>r,"h","v"),
         vt,XTYPE(lv),
         e,errors,
         b,blanks,
         l,logicals,
         aa,IF(vt="dt:BLANK",
            IFERROR(IFS(AND(e="",b="",l=""),IF(XTYPE(a)="dt:BLANK",1,0),AND(b="",l=""),IF(e=-1,IF(XTYPE(a)="dt:ERROR",1,0),IFERROR(IF(ERROR.TYPE(a)=e,1,0),0)),AND(e="",l=""),SWITCH(b,0,IF(XTYPE(a)="dt:BLANK",1,0),1,IF(XTYPE(a,1)="dt:BLANK_REAL",1,0),2,IF(XTYPE(a,1)="dt:BLANK_FORMULA",1,0)),AND(e="",b=""),IF(l=-1,IF(XTYPE(a)="dt:LOGICAL",1,0),SWITCH(l,1,IF(XTYPE(a,1)="dt:LOGICAL_TRUE",1,0),0,IF(XTYPE(a,1)="dt:LOGICAL_FALSE",1,0)))),NA()),
            IF(AND(e="",b="",l=""),IFS(vt="dt:NUMBER",IF(XTYPE(a)="dt:NUMBER",a,""),vt="dt:TEXT",IF(XTYPE(a)="dt:TEXT",a,""),vt="dt:LOGICAL",LET(tf,XTYPE(lv,1),IFS(tf="dt:LOGICAL_TRUE",IF(XTYPE(a,1)="dt:LOGICAL_TRUE",1,0),tf="dt:LOGICAL_FALSE",IF(XTYPE(a,1)="dt:LOGICAL_FALSE",1,0)))),NA())),
               FILTER(
                  IF(hv="h",SEQUENCE(,c),SEQUENCE(r,)),
                     LET(
                        mt,match_type,
                           IFS(
                              vt="dt:NUMBER",IF(mt="",(aa=lv)*(ISNUMBER(aa)),ISNUMBER(aa)*SWITCH(mt,1,aa>lv,2,aa>=lv,-1,aa<lv,-2,aa<=lv,0,aa=lv)),
                              vt="dt:TEXT",IF(OR(mt="",match_type=0),(IF(XTYPE(aa)="dt:TEXT",1,0))*(aa=lv)),
                              vt="dt:LOGICAL",IF(OR(mt="",match_type=0),IFS(lv=TRUE,aa,lv=FALSE,aa)),
                              vt="dt:BLANK",IFS(AND(e="",b="",l=""),IF(OR(mt="",mt=0),aa),AND(e="",l=""),IF(OR(mt="",mt=0),aa),AND(e="",b=""),IF(OR(mt="",mt=0),IF(lv="",aa)),AND(b="",l=""),IF(OR(mt="",mt=0),IF(lv="",aa)))
                           )
                     )
               )
      ),
      IF(ISERROR(exp),IF(ERROR.TYPE(exp)=14,NA(),exp),exp)
   )
)

matches.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		#CALC!
3		data type:	number	number	logical (false)	number	text	(real) blank	error	logical (true)	error	text	(formula) blank	number
4		cells:	1	2	FALSE	-2	&		#DIV/0!	TRUE	#REF!	b		6
5		position:	1	2	3	4	5	6	7	8	9	10	11	12
6		XTYPE($C$4:$N$4):	dt:NUMBER	dt:NUMBER	dt:LOGICAL	dt:NUMBER	dt:TEXT	dt:BLANK	dt:ERROR	dt:LOGICAL	dt:ERROR	dt:TEXT	dt:BLANK	dt:NUMBER
7		XTYPE($C$4:$N$4,1):	dt:NUMBER	dt:NUMBER	dt:LOGICAL_FALSE	dt:NUMBER	dt:TEXT	dt:BLANK_REAL	dt:ERROR_DIV/0!	dt:LOGICAL_TRUE	dt:ERROR_REF!	dt:TEXT	dt:BLANK_FORMULA	dt:NUMBER
8
9
10		match_type	existing number lookup						match_type	nonexisting number lookup
11		none	1						none	#N/A
12		0	1						0	#N/A
13		1	2	12					1	12
14		2	1	2	12				2	12
15		-1	4						-1	1	2	4
16		-2	1	4					-2	1	2	4
17
18		match_type	existing text lookup						match_type	nonexisting text lookup
19		none	10						none	#N/A
20		0	10						0	#N/A
21		1	#N/A						1	#N/A
22		2	#N/A						2	#N/A
23		-1	#N/A						-1	#N/A
24		-2	#N/A						-2	#N/A
25
26		match_type	all blanks lookup (method 1)			match_type	all blanks lookup (method 2)			match_type	all blanks lookup (method 3)			match_type	all blanks lookup (method 4)
27		none	6	11		none	6	11		none	6	11		none	6	11
28		0	6	11		0	6	11		0	6	11		0	6	11
29		1	#N/A			1	#N/A			1	#N/A			1	#N/A
30		2	#N/A			2	#N/A			2	#N/A			2	#N/A
31		-1	#N/A			-1	#N/A			-1	#N/A			-1	#N/A
32		-2	#N/A			-2	#N/A			-2	#N/A			-2	#N/A
33						match_type	Specific blanks lookup (method 1)							match_type	Specific blanks lookup (method 2)
34						none	6							none	6
35						0	6							0	6
36						1	#N/A							1	#N/A
37						2	#N/A							2	#N/A
38						-1	#N/A							-1	#N/A
39						-2	#N/A							-2	#N/A
40						none	11							none	11
41						0	11							0	11
42						1	#N/A							1	#N/A
43						2	#N/A							2	#N/A
44						-1	#N/A							-1	#N/A
45						-2	#N/A							-2	#N/A
46
47		match_type	all errors lookup (method 1)						match_type	all errors lookup (method 2)
48		none	7	9					none	7	9
49		0	7	9					0	7	9
50		1	#N/A						1	#N/A
51		2	#N/A						2	#N/A
52		-1	#N/A						-1	#N/A
53		-2	#N/A						-2	#N/A
54		match_type	specific errors lookup (method 1)			match_type	specific errors lookup (method 2)			match_type	specific errors lookup (method 3)			match_type	specific errors lookup (method 4)
55		none	#N/A			0	#N/A			none	#N/A			0	#N/A
56		none	#N/A			0	#N/A			none	#N/A			0	#N/A
57		none	7			0	7			none	7			0	7
58		none	#N/A			0	#N/A			none	#N/A			0	#N/A
59		none	9			0	9			none	9			0	9
60		none	#N/A			0	#N/A			none	#N/A			0	#N/A
61		none	#N/A			0	#N/A			none	#N/A			0	#N/A
62		none	#N/A			0	#N/A			none	#N/A			0	#N/A
63		none	#N/A			0	#N/A			none	#N/A			0	#N/A
64		none	#N/A			0	#N/A			none	#N/A			0	#N/A
65		none	#N/A			0	#N/A			none	#N/A			0	#N/A
66		none	#N/A			0	#N/A			none	#N/A			0	#N/A
67		none	#N/A			0	#N/A			none	#N/A			0	#N/A
68		none	#N/A			0	#N/A			none	#N/A			0	#N/A
69		none	#N/A			0	#N/A			none	#N/A			0	#N/A
70
71		match_type	TRUE logicals lookup (method 1)			match_type	TRUE logicals lookup (method 2)			match_type	TRUE logicals lookup (method 3)			match_type	all logicals lookup (method 1)
72		none	8			none	8			none	8			none	3	8
73		0	8			0	8			0	8			0	3	8
74		1	#N/A			1	#N/A			1	#N/A			1	#N/A
75		2	#N/A			2	#N/A			2	#N/A			2	#N/A
76		-1	#N/A			-1	#N/A			-1	#N/A			-1	#N/A
77		-2	#N/A			-2	#N/A			-2	#N/A			-2	#N/A
78		match_type	FALSE logicals lookup (method 1)			match_type	FALSE logicals lookup (method 2)			match_type	FALSE logicals lookup (method 3)			match_type	all logicals lookup (method 2)
79		none	3			none	3			none	3			none	3	8
80		0	3			0	3			0	3			0	3	8
81		1	#N/A			1	#N/A			1	#N/A			1	#N/A
82		2	#N/A			2	#N/A			2	#N/A			2	#N/A
83		-1	#N/A			-1	#N/A			-1	#N/A			-1	#N/A
84		-2	#N/A			-2	#N/A			-2	#N/A			-2	#N/A
85
correct_params_horizontal

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(lookup_value,lookup_1D_array,[match_type],[errors],[blanks],[logicals],LET(exp,LET(lv,lookup_value,a,lookup_1D_array,c,COLUMNS(a),r,ROWS(a),hv,IF(c>r,"h","v"),vt,XTYPE(lv),e,errors,b,blanks,l,logicals,aa,IF(vt="dt:BLANK",IFERROR(IFS(AND(e="",b="",l=""),IF(XTYPE(a)="dt:BLANK",1,0),AND(b="",l=""),IF(e=-1,IF(XTYPE(a)="dt:ERROR",1,0),IFERROR(IF(ERROR.TYPE(a)=e,1,0),0)),AND(e="",l=""),SWITCH(b,0,IF(XTYPE(a)="dt:BLANK",1,0),1,IF(XTYPE(a,1)="dt:BLANK_REAL",1,0),2,IF(XTYPE(a,1)="dt:BLANK_FORMULA",1,0)),AND(e="",b=""),IF(l=-1,IF(XTYPE(a)="dt:LOGICAL",1,0),SWITCH(l,1,IF(XTYPE(a,1)="dt:LOGICAL_TRUE",1,0),0,IF(XTYPE(a,1)="dt:LOGICAL_FALSE",1,0)))),NA()),IF(AND(e="",b="",l=""),IFS(vt="dt:NUMBER",IF(XTYPE(a)="dt:NUMBER",a,""),vt="dt:TEXT",IF(XTYPE(a)="dt:TEXT",a,""),vt="dt:LOGICAL",LET(tf,XTYPE(lv,1),IFS(tf="dt:LOGICAL_TRUE",IF(XTYPE(a,1)="dt:LOGICAL_TRUE",1,0),tf="dt:LOGICAL_FALSE",IF(XTYPE(a,1)="dt:LOGICAL_FALSE",1,0)))),NA())),FILTER(IF(hv="h",SEQUENCE(,c),SEQUENCE(r,)),LET(mt,match_type,IFS(vt="dt:NUMBER",IF(mt="",(aa=lv)*(ISNUMBER(aa)),ISNUMBER(aa)*SWITCH(mt,1,aa>lv,2,aa>=lv,-1,aa<lv,-2,aa<=lv,0,aa=lv)),vt="dt:TEXT",IF(OR(mt="",match_type=0),(IF(XTYPE(aa)="dt:TEXT",1,0))*(aa=lv)),vt="dt:LOGICAL",IF(OR(mt="",match_type=0),IFS(lv=TRUE,aa,lv=FALSE,aa)),vt="dt:BLANK",IFS(AND(e="",b="",l=""),IF(OR(mt="",mt=0),aa),AND(e="",l=""),IF(OR(mt="",mt=0),aa),AND(e="",b=""),IF(OR(mt="",mt=0),IF(lv="",aa)),AND(b="",l=""),IF(OR(mt="",mt=0),IF(lv="",aa))))))),IF(ISERROR(exp),IF(ERROR.TYPE(exp)=14,NA(),exp),exp)))
I4	I4	=1/0
K4	K4	=LOOKUP(#REF!,#REF!)
M4	M4	=IF(M1>2,1,"")
C6:N6	C6	=XTYPE(C4:N4)
C7:N7	C7	=XTYPE(C4:N4,1)
P14	P14	=LET(x,"",TEXT(x,10))
C11	C11	=MATCHES(1,C4:N4)
C12	C12	=MATCHES(1,C4:N4,0)
C13:D13	C13	=MATCHES(1,C4:N4,1)
C14:E14	C14	=MATCHES(1,C4:N4,2)
C15	C15	=MATCHES(1,C4:N4,-1)
C16:D16	C16	=MATCHES(1,C4:N4,-2)
J11	J11	=MATCHES(5,$C$4:$N$4)
J12	J12	=MATCHES(5,$C$4:$N$4,0)
J13	J13	=MATCHES(5,$C$4:$N$4,1)
J14	J14	=MATCHES(5,$C$4:$N$4,2)
J15:L15	J15	=MATCHES(5,$C$4:$N$4,-1)
J16:L16	J16	=MATCHES(5,$C$4:$N$4,-2)
C19	C19	=MATCHES("b",C4:N4)
C20	C20	=MATCHES("b",C4:N4,0)
C21	C21	=MATCHES("b",C4:N4,1)
C22	C22	=MATCHES("b",C4:N4,2)
C23	C23	=MATCHES("b",C4:N4,-1)
C24	C24	=MATCHES("b",C4:N4,-2)
J19	J19	=MATCHES("app",$C$4:$N$4)
J20	J20	=MATCHES("app",$C$4:$N$4,0)
J21	J21	=MATCHES("app",$C$4:$N$4,1)
J22	J22	=MATCHES("app",$C$4:$N$4,2)
J23	J23	=MATCHES("app",$C$4:$N$4,-1)
J24	J24	=MATCHES("app",$C$4:$N$4,-2)
C27:D27	C27	=MATCHES(,$C$4:$N$4,)
C28:D28	C28	=MATCHES(,$C$4:$N$4,0)
C29	C29	=MATCHES(,$C$4:$N$4,1)
C30	C30	=MATCHES(,$C$4:$N$4,2)
C31	C31	=MATCHES(,$C$4:$N$4,-1)
C32	C32	=MATCHES(,$C$4:$N$4,-2)
G27:H27	G27	=MATCHES(,$C$4:$N$4,,,0)
G28:H28	G28	=MATCHES(,$C$4:$N$4,0,,0)
G29	G29	=MATCHES(,$C$4:$N$4,1,,0)
G30	G30	=MATCHES(,$C$4:$N$4,2,,0)
G31	G31	=MATCHES(,$C$4:$N$4,-1,,0)
G32	G32	=MATCHES(,$C$4:$N$4,-2,,0)
K27:L27	K27	=MATCHES("",$C$4:$N$4,)
K28:L28	K28	=MATCHES("",$C$4:$N$4,0)
K29	K29	=MATCHES("",$C$4:$N$4,1)
K30	K30	=MATCHES("",$C$4:$N$4,2)
K31	K31	=MATCHES("",$C$4:$N$4,-1)
K32	K32	=MATCHES("",$C$4:$N$4,-2)
O27:P27	O27	=MATCHES("",$C$4:$N$4,,,0)
O28:P28	O28	=MATCHES("",$C$4:$N$4,0,,0)
O29	O29	=MATCHES("",$C$4:$N$4,1,,0)
O30	O30	=MATCHES("",$C$4:$N$4,2,,0)
O31	O31	=MATCHES("",$C$4:$N$4,-1,,0)
O32	O32	=MATCHES("",$C$4:$N$4,-2,,0)
G34	G34	=MATCHES(,$C$4:$N$4,,,1)
G35	G35	=MATCHES(,$C$4:$N$4,0,,1)
G36	G36	=MATCHES(,$C$4:$N$4,1,,1)
G37	G37	=MATCHES(,$C$4:$N$4,2,,1)
G38	G38	=MATCHES(,$C$4:$N$4,-1,,1)
G39	G39	=MATCHES(,$C$4:$N$4,-2,,1)
G40	G40	=MATCHES(,$C$4:$N$4,,,2)
G41	G41	=MATCHES(,$C$4:$N$4,0,,2)
G42	G42	=MATCHES(,$C$4:$N$4,1,,2)
G43	G43	=MATCHES(,$C$4:$N$4,2,,2)
G44	G44	=MATCHES(,$C$4:$N$4,-1,,2)
G45	G45	=MATCHES(,$C$4:$N$4,-2,,2)
O34	O34	=MATCHES("",$C$4:$N$4,,,1)
O35	O35	=MATCHES("",$C$4:$N$4,0,,1)
O36	O36	=MATCHES("",$C$4:$N$4,1,,1)
O37	O37	=MATCHES("",$C$4:$N$4,2,,1)
O38	O38	=MATCHES("",$C$4:$N$4,-1,,1)
O39	O39	=MATCHES("",$C$4:$N$4,-2,,1)
O40	O40	=MATCHES("",$C$4:$N$4,,,2)
O41	O41	=MATCHES("",$C$4:$N$4,0,,2)
O42	O42	=MATCHES("",$C$4:$N$4,1,,2)
O43	O43	=MATCHES("",$C$4:$N$4,2,,2)
O44	O44	=MATCHES("",$C$4:$N$4,-1,,2)
O45	O45	=MATCHES("",$C$4:$N$4,-2,,2)
C48:D48	C48	=MATCHES(,$C$4:$N$4,,-1)
C49:D49	C49	=MATCHES(,$C$4:$N$4,0,-1)
C50	C50	=MATCHES(,$C$4:$N$4,1,-1)
C51	C51	=MATCHES(,$C$4:$N$4,2,-1)
C52	C52	=MATCHES(,$C$4:$N$4,-1,-1)
C53	C53	=MATCHES(,$C$4:$N$4,-2,-1)
J48:K48	J48	=MATCHES("",$C$4:$N$4,,-1)
J49:K49	J49	=MATCHES("",$C$4:$N$4,0,-1)
J50	J50	=MATCHES("",$C$4:$N$4,1,-1)
J51	J51	=MATCHES("",$C$4:$N$4,2,-1)
J52	J52	=MATCHES("",$C$4:$N$4,-1,-1)
J53	J53	=MATCHES("",$C$4:$N$4,-2,-1)
C55	C55	=MATCHES(,$C$4:$N$4,,0)
C56	C56	=MATCHES(,$C$4:$N$4,,1)
C57	C57	=MATCHES(,$C$4:$N$4,,2)
C58	C58	=MATCHES(,$C$4:$N$4,,3)
C59	C59	=MATCHES(,$C$4:$N$4,,4)
C60	C60	=MATCHES(,$C$4:$N$4,,5)
C61	C61	=MATCHES(,$C$4:$N$4,,6)
C62	C62	=MATCHES(,$C$4:$N$4,,7)
C63	C63	=MATCHES(,$C$4:$N$4,,8)
C64	C64	=MATCHES(,$C$4:$N$4,,9)
C65	C65	=MATCHES(,$C$4:$N$4,,10)
C66	C66	=MATCHES(,$C$4:$N$4,,11)
C67	C67	=MATCHES(,$C$4:$N$4,,12)
C68	C68	=MATCHES(,$C$4:$N$4,,13)
C69	C69	=MATCHES(,$C$4:$N$4,,14)
G55:G56	G55	=MATCHES(,$C$4:$N$4,0,1)
G57	G57	=MATCHES(,$C$4:$N$4,0,2)
G58	G58	=MATCHES(,$C$4:$N$4,0,3)
G59	G59	=MATCHES(,$C$4:$N$4,0,4)
G60	G60	=MATCHES(,$C$4:$N$4,0,5)
G61	G61	=MATCHES(,$C$4:$N$4,0,6)
G62	G62	=MATCHES(,$C$4:$N$4,0,7)
G63	G63	=MATCHES(,$C$4:$N$4,0,8)
G64	G64	=MATCHES(,$C$4:$N$4,0,9)
G65	G65	=MATCHES(,$C$4:$N$4,0,10)
G66	G66	=MATCHES(,$C$4:$N$4,0,11)
G67	G67	=MATCHES(,$C$4:$N$4,0,12)
G68	G68	=MATCHES(,$C$4:$N$4,0,13)
G69	G69	=MATCHES(,$C$4:$N$4,0,14)
K55:K56	K55	=MATCHES("",$C$4:$N$4,,1)
K57	K57	=MATCHES("",$C$4:$N$4,,2)
K58	K58	=MATCHES("",$C$4:$N$4,,3)
K59	K59	=MATCHES("",$C$4:$N$4,,4)
K60	K60	=MATCHES("",$C$4:$N$4,,5)
K61	K61	=MATCHES("",$C$4:$N$4,,6)
K62	K62	=MATCHES("",$C$4:$N$4,,7)
K63	K63	=MATCHES("",$C$4:$N$4,,8)
K64	K64	=MATCHES("",$C$4:$N$4,,9)
K65	K65	=MATCHES("",$C$4:$N$4,,10)
K66	K66	=MATCHES("",$C$4:$N$4,,11)
K67	K67	=MATCHES("",$C$4:$N$4,,12)
K68	K68	=MATCHES("",$C$4:$N$4,,13)
K69	K69	=MATCHES("",$C$4:$N$4,,14)
O55:O56	O55	=MATCHES("",$C$4:$N$4,0,1)
O57	O57	=MATCHES("",$C$4:$N$4,0,2)
O58	O58	=MATCHES("",$C$4:$N$4,0,3)
O59	O59	=MATCHES("",$C$4:$N$4,0,4)
O60	O60	=MATCHES("",$C$4:$N$4,0,5)
O61	O61	=MATCHES("",$C$4:$N$4,0,6)
O62	O62	=MATCHES("",$C$4:$N$4,0,7)
O63	O63	=MATCHES("",$C$4:$N$4,0,8)
O64	O64	=MATCHES("",$C$4:$N$4,0,9)
O65	O65	=MATCHES("",$C$4:$N$4,0,10)
O66	O66	=MATCHES("",$C$4:$N$4,0,11)
O67	O67	=MATCHES("",$C$4:$N$4,0,12)
O68	O68	=MATCHES("",$C$4:$N$4,0,13)
O69	O69	=MATCHES("",$C$4:$N$4,0,14)
C72	C72	=MATCHES(TRUE,$C$4:$N$4)
C73	C73	=MATCHES(TRUE,$C$4:$N$4,0)
C74	C74	=MATCHES(TRUE,$C$4:$N$4,1)
C75	C75	=MATCHES(TRUE,$C$4:$N$4,2)
C76	C76	=MATCHES(TRUE,$C$4:$N$4,-1)
C77	C77	=MATCHES(TRUE,$C$4:$N$4,-2)
G72	G72	=MATCHES(,$C$4:$N$4,,,,1)
G73	G73	=MATCHES(,$C$4:$N$4,0,,,1)
G74	G74	=MATCHES(,$C$4:$N$4,1,,,1)
G75	G75	=MATCHES(,$C$4:$N$4,2,,,1)
G76	G76	=MATCHES(,$C$4:$N$4,-1,,,1)
G77	G77	=MATCHES(,$C$4:$N$4,-2,,,1)
K72	K72	=MATCHES("",$C$4:$N$4,,,,1)
K73	K73	=MATCHES("",$C$4:$N$4,0,,,1)
K74	K74	=MATCHES("",$C$4:$N$4,1,,,1)
K75	K75	=MATCHES("",$C$4:$N$4,2,,,1)
K76	K76	=MATCHES("",$C$4:$N$4,-1,,,1)
K77	K77	=MATCHES("",$C$4:$N$4,-2,,,1)
O72:P72	O72	=MATCHES(,$C$4:$N$4,,,,-1)
O73:P73	O73	=MATCHES(,$C$4:$N$4,0,,,-1)
O74	O74	=MATCHES(,$C$4:$N$4,1,,,-1)
O75	O75	=MATCHES(,$C$4:$N$4,2,,,-1)
O76	O76	=MATCHES(,$C$4:$N$4,-1,,,-1)
O77	O77	=MATCHES(,$C$4:$N$4,-2,,,-1)
C79	C79	=MATCHES(FALSE,$C$4:$N$4)
C80	C80	=MATCHES(FALSE,$C$4:$N$4,0)
C81	C81	=MATCHES(FALSE,$C$4:$N$4,1)
C82	C82	=MATCHES(FALSE,$C$4:$N$4,2)
C83	C83	=MATCHES(FALSE,$C$4:$N$4,-1)
C84	C84	=MATCHES(FALSE,$C$4:$N$4,-2)
G79	G79	=MATCHES(,$C$4:$N$4,,,,0)
G80	G80	=MATCHES(,$C$4:$N$4,0,,,0)
G81	G81	=MATCHES(,$C$4:$N$4,1,,,0)
G82	G82	=MATCHES(,$C$4:$N$4,2,,,0)
G83	G83	=MATCHES(,$C$4:$N$4,-1,,,0)
G84	G84	=MATCHES(,$C$4:$N$4,-2,,,0)
K79	K79	=MATCHES("",$C$4:$N$4,,,,0)
K80	K80	=MATCHES("",$C$4:$N$4,0,,,0)
K81	K81	=MATCHES("",$C$4:$N$4,1,,,0)
K82	K82	=MATCHES("",$C$4:$N$4,2,,,0)
K83	K83	=MATCHES("",$C$4:$N$4,-1,,,0)
K84	K84	=MATCHES("",$C$4:$N$4,-2,,,0)
O79:P79	O79	=MATCHES("",$C$4:$N$4,,,,-1)
O80:P80	O80	=MATCHES("",$C$4:$N$4,0,,,-1)
O81	O81	=MATCHES("",$C$4:$N$4,1,,,-1)
O82	O82	=MATCHES("",$C$4:$N$4,2,,,-1)
O83	O83	=MATCHES("",$C$4:$N$4,-1,,,-1)
O84	O84	=MATCHES("",$C$4:$N$4,-2,,,-1)
Dynamic array formulas.

The following XL2BB shows that the function returns errors when the wrong arguments are used:

matches.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		#CALC!
3		type:	number	number	logical (false)	number	text	(real) blank	error	logical (true)	error	text	(formula) blank	number
4		cells:	1	2	FALSE	-2	&		#DIV/0!	TRUE	#N/A	b		6
5		position:	1	2	3	4	5	6	7	8	9	10	11	12
6
7
8			wrong params	existing		wrong params	nonexisting		wrong params	existing		wrong params	nonexisting
9				#N/A			#N/A			#N/A			#N/A
10				#N/A			#N/A			#N/A			#N/A
11				#N/A			#N/A			#N/A			#N/A
12				#N/A			#N/A			#N/A			#N/A
13				#N/A			#N/A			#N/A			#N/A
14				#N/A			#N/A			#N/A			#N/A
15				#N/A			#N/A			#N/A			#N/A
16				#N/A			#N/A			#N/A			#N/A
17				#N/A			#N/A			#N/A			#N/A
18				#N/A			#N/A			#N/A			#N/A
19				#N/A			#N/A			#N/A			#N/A
20				#N/A			#N/A			#N/A			#N/A
21				#N/A			#N/A			#N/A			#N/A
22				#N/A			#N/A			#N/A			#N/A
23				#N/A			#N/A			#N/A			#N/A
24				#N/A			#N/A			#N/A			#N/A
25				#N/A			#N/A			#N/A			#N/A
26				#N/A			#N/A			#N/A			#N/A
27				#N/A			#N/A			#N/A			#N/A
28				#N/A			#N/A			#N/A			#N/A
29				#N/A			#N/A			#N/A			#N/A
30				#N/A			#N/A			#N/A			#N/A
31				#N/A			#N/A			#N/A			#N/A
32				#N/A			#N/A			#N/A			#N/A
33				#N/A			#N/A			#N/A			#N/A
34				#N/A			#N/A			#N/A			#N/A
35				#N/A			#N/A			#N/A			#N/A
36				#N/A			#N/A			#N/A			#N/A
37				#N/A			#N/A			#N/A			#N/A
38				#N/A			#N/A			#N/A			#N/A
39			other
40				#N/A			#N/A			#N/A			#N/A
41				#N/A			#N/A			#N/A			#N/A
42				#N/A			#N/A			#N/A			#N/A
43				#N/A			#N/A			#N/A			#N/A
44				#N/A			#N/A			#N/A			#N/A
45				#N/A			#N/A			#N/A			#N/A
46				#N/A			#N/A			#N/A			#N/A
47
incorrect_params

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(lookup_value,lookup_array,[match_type],[errors],[blanks],LET(exp,LET(a,lookup_array,c,COLUMNS(a),r,ROWS(a),hv,IF(c>r,"h","v"),vt,XTYPE(lookup_value),aa,IF(blanks="",IF(errors="",IF(vt="dt:NUMBER",IF(XTYPE(a)="dt:NUMBER",a,""),IF(vt="dt:TEXT",IF(XTYPE(a)="dt:TEXT",a,""),IF(vt="dt:BLANK",IF(XTYPE(a)="dt:BLANK","",0)))),IF(errors=0,IF(ISERROR(a),1,0),IFERROR(IF(ERROR.TYPE(a)=errors,1,0),0))),IF(blanks=0,IF(errors="",IF(vt="dt:NUMBER",IF(XTYPE(a)="dt:NUMBER",a,""),IF(vt="dt:TEXT",IF(XTYPE(a)="dt:TEXT",a,""),IF(vt="dt:BLANK",IF(XTYPE(a)="dt:BLANK","",0)))),NA()),IF(errors="",IF(blanks=1,IF(vt="dt:BLANK",IF(XTYPE(a,1)="dt:BLANK_REAL","",0)),IF(blanks=2,IF(vt="dt:BLANK",IF(XTYPE(a,1)="dt:BLANK_FORMULA","",0)),NA())),NA()))),FILTER(IF(hv="h",SEQUENCE(,c),SEQUENCE(r,)),IF(vt="dt:NUMBER",SWITCH(match_type,1,(aa>lookup_value)*(ISNUMBER(aa)),2,(aa>=lookup_value)*(ISNUMBER(aa)),-1,(aa<lookup_value)*(ISNUMBER(aa)),-2,(aa<=lookup_value)*(ISNUMBER(aa)),0,(aa=lookup_value)*(ISNUMBER(aa)),(aa=lookup_value)*(ISNUMBER(aa))),IF(vt="dt:TEXT",SWITCH(match_type,1,NA(),2,NA(),-1,NA(),-2,NA(),0,(IF(XTYPE(aa)="dt:TEXT",1,0))*(aa=lookup_value),(IF(XTYPE(aa)="dt:TEXT",1,0))*(aa=lookup_value)),IF(vt="dt:BLANK",IF(errors="",SWITCH(match_type,1,NA(),2,NA(),-1,NA(),-2,NA(),0,IF(XTYPE(aa)="dt:BLANK",1,0),IF(XTYPE(aa)="dt:BLANK",1,0)),IF(OR(match_type="",match_type=0),aa,NA()))))))),IF(ISERROR(exp),IF(ERROR.TYPE(exp)=14,NA(),exp),exp)))
I4	I4	=1/0
K4	K4	=LOOKUP(D2,F2:G2)
M4	M4	=IF(K2>2,1,"")
D9	D9	=MATCHES(1,$C$4:$N$4,,0,)
D10	D10	=MATCHES(1,$C$4:$N$4,,,0)
D11	D11	=MATCHES(1,$C$4:$N$4,,0,0)
D12	D12	=MATCHES(1,$C$4:$N$4,,1,)
D13	D13	=MATCHES(1,$C$4:$N$4,,,1)
D14	D14	=MATCHES(1,$C$4:$N$4,,1,1)
D15	D15	=MATCHES(1,$C$4:$N$4,,2,)
D16	D16	=MATCHES(1,$C$4:$N$4,,,2)
D17	D17	=MATCHES(1,$C$4:$N$4,,2,2)
D18	D18	=MATCHES(1,$C$4:$N$4,,3,)
D19	D19	=MATCHES(1,$C$4:$N$4,,,3)
D20	D20	=MATCHES(1,$C$4:$N$4,,3,3)
D21	D21	=MATCHES(1,$C$4:$N$4,,7,)
D22	D22	=MATCHES(1,$C$4:$N$4,,,7)
D23	D23	=MATCHES(1,$C$4:$N$4,,7,7)
D24	D24	=MATCHES("b",$C$4:$N$4,,0,)
D25	D25	=MATCHES("b",$C$4:$N$4,,,0)
D26	D26	=MATCHES("b",$C$4:$N$4,,0,0)
D27	D27	=MATCHES("b",$C$4:$N$4,,1,)
D28	D28	=MATCHES("b",$C$4:$N$4,,,1)
D29	D29	=MATCHES("b",$C$4:$N$4,,1,1)
D30	D30	=MATCHES("b",$C$4:$N$4,,2,)
D31	D31	=MATCHES("b",$C$4:$N$4,,,2)
D32	D32	=MATCHES("b",$C$4:$N$4,,2,2)
D33	D33	=MATCHES("b",$C$4:$N$4,,3,)
D34	D34	=MATCHES("b",$C$4:$N$4,,,3)
D35	D35	=MATCHES("b",$C$4:$N$4,,3,3)
D36	D36	=MATCHES("b",$C$4:$N$4,,7,)
D37	D37	=MATCHES("b",$C$4:$N$4,,,7)
D38	D38	=MATCHES("b",$C$4:$N$4,,7,7)
G9	G9	=MATCHES(4,$C$4:$N$4,,0,)
G10	G10	=MATCHES(4,$C$4:$N$4,,,0)
G11	G11	=MATCHES(4,$C$4:$N$4,,0,0)
G12	G12	=MATCHES(4,$C$4:$N$4,,1,)
G13	G13	=MATCHES(4,$C$4:$N$4,,,1)
G14	G14	=MATCHES(4,$C$4:$N$4,,1,1)
G15	G15	=MATCHES(4,$C$4:$N$4,,2,)
G16	G16	=MATCHES(4,$C$4:$N$4,,,2)
G17	G17	=MATCHES(4,$C$4:$N$4,,2,2)
G18	G18	=MATCHES(4,$C$4:$N$4,,3,)
G19	G19	=MATCHES(4,$C$4:$N$4,,,3)
G20	G20	=MATCHES(4,$C$4:$N$4,,3,3)
G21	G21	=MATCHES(4,$C$4:$N$4,,7,)
G22	G22	=MATCHES(4,$C$4:$N$4,,,7)
G23	G23	=MATCHES(4,$C$4:$N$4,,7,7)
G24	G24	=MATCHES("a",$C$4:$N$4,,0,)
G25	G25	=MATCHES("a",$C$4:$N$4,,,0)
G26	G26	=MATCHES("a",$C$4:$N$4,,0,0)
G27	G27	=MATCHES("a",$C$4:$N$4,,1,)
G28	G28	=MATCHES("a",$C$4:$N$4,,,1)
G29	G29	=MATCHES("a",$C$4:$N$4,,1,1)
G30	G30	=MATCHES("a",$C$4:$N$4,,2,)
G31	G31	=MATCHES("a",$C$4:$N$4,,,2)
G32	G32	=MATCHES("a",$C$4:$N$4,,2,2)
G33	G33	=MATCHES("a",$C$4:$N$4,,3,)
G34	G34	=MATCHES("a",$C$4:$N$4,,,3)
G35	G35	=MATCHES("a",$C$4:$N$4,,3,3)
G36	G36	=MATCHES("a",$C$4:$N$4,,7,)
G37	G37	=MATCHES("a",$C$4:$N$4,,,7)
G38	G38	=MATCHES("a",$C$4:$N$4,,7,7)
J9	J9	=MATCHES(1,$C$4:$N$4,0,0,)
J10	J10	=MATCHES(1,$C$4:$N$4,0,,0)
J11	J11	=MATCHES(1,$C$4:$N$4,0,0,0)
J12	J12	=MATCHES(1,$C$4:$N$4,0,1,)
J13	J13	=MATCHES(1,$C$4:$N$4,0,,1)
J14	J14	=MATCHES(1,$C$4:$N$4,0,1,1)
J15	J15	=MATCHES(1,$C$4:$N$4,0,2,)
J16	J16	=MATCHES(1,$C$4:$N$4,0,,2)
J17	J17	=MATCHES(1,$C$4:$N$4,0,2,2)
J18	J18	=MATCHES(1,$C$4:$N$4,0,3,)
J19	J19	=MATCHES(1,$C$4:$N$4,0,,3)
J20	J20	=MATCHES(1,$C$4:$N$4,0,3,3)
J21	J21	=MATCHES(1,$C$4:$N$4,0,7,)
J22	J22	=MATCHES(1,$C$4:$N$4,0,,7)
J23	J23	=MATCHES(1,$C$4:$N$4,0,7,7)
J24	J24	=MATCHES("b",$C$4:$N$4,0,0,)
J25	J25	=MATCHES("b",$C$4:$N$4,0,,0)
J26	J26	=MATCHES("b",$C$4:$N$4,0,0,0)
J27	J27	=MATCHES("b",$C$4:$N$4,0,1,)
J28	J28	=MATCHES("b",$C$4:$N$4,0,,1)
J29	J29	=MATCHES("b",$C$4:$N$4,0,1,1)
J30	J30	=MATCHES("b",$C$4:$N$4,0,2,)
J31	J31	=MATCHES("b",$C$4:$N$4,0,,2)
J32	J32	=MATCHES("b",$C$4:$N$4,0,2,2)
J33	J33	=MATCHES("b",$C$4:$N$4,0,3,)
J34	J34	=MATCHES("b",$C$4:$N$4,0,,3)
J35	J35	=MATCHES("b",$C$4:$N$4,0,3,3)
J36	J36	=MATCHES("b",$C$4:$N$4,0,7,)
J37	J37	=MATCHES("b",$C$4:$N$4,0,,7)
J38	J38	=MATCHES("b",$C$4:$N$4,0,7,7)
M9	M9	=MATCHES(4,$C$4:$N$4,0,0,)
M10	M10	=MATCHES(4,$C$4:$N$4,0,,0)
M11	M11	=MATCHES(4,$C$4:$N$4,0,0,0)
M12	M12	=MATCHES(4,$C$4:$N$4,0,1,)
M13	M13	=MATCHES(4,$C$4:$N$4,0,,1)
M14	M14	=MATCHES(4,$C$4:$N$4,0,1,1)
M15	M15	=MATCHES(4,$C$4:$N$4,0,2,)
M16	M16	=MATCHES(4,$C$4:$N$4,0,,2)
M17	M17	=MATCHES(4,$C$4:$N$4,0,2,2)
M18	M18	=MATCHES(4,$C$4:$N$4,0,3,)
M19	M19	=MATCHES(4,$C$4:$N$4,0,,3)
M20	M20	=MATCHES(4,$C$4:$N$4,0,3,3)
M21	M21	=MATCHES(4,$C$4:$N$4,0,7,)
M22	M22	=MATCHES(4,$C$4:$N$4,0,,7)
M23	M23	=MATCHES(4,$C$4:$N$4,0,7,7)
M24	M24	=MATCHES("a",$C$4:$N$4,0,0,)
M25	M25	=MATCHES("a",$C$4:$N$4,0,,0)
M26	M26	=MATCHES("a",$C$4:$N$4,0,0,0)
M27	M27	=MATCHES("a",$C$4:$N$4,0,1,)
M28	M28	=MATCHES("a",$C$4:$N$4,0,,1)
M29	M29	=MATCHES("a",$C$4:$N$4,0,1,1)
M30	M30	=MATCHES("a",$C$4:$N$4,0,2,)
M31	M31	=MATCHES("a",$C$4:$N$4,0,,2)
M32	M32	=MATCHES("a",$C$4:$N$4,0,2,2)
M33	M33	=MATCHES("a",$C$4:$N$4,0,3,)
M34	M34	=MATCHES("a",$C$4:$N$4,0,,3)
M35	M35	=MATCHES("a",$C$4:$N$4,0,3,3)
M36	M36	=MATCHES("a",$C$4:$N$4,0,7,)
M37	M37	=MATCHES("a",$C$4:$N$4,0,,7)
M38	M38	=MATCHES("a",$C$4:$N$4,0,7,7)
D40	D40	=MATCHES("",$C$4:$N$4,,0,0)
D41	D41	=MATCHES("",$C$4:$N$4,,1,1)
D42	D42	=MATCHES("",$C$4:$N$4,,2,2)
D43	D43	=MATCHES("",$C$4:$N$4,,,3)
D44	D44	=MATCHES("",$C$4:$N$4,,3,3)
D45	D45	=MATCHES("",$C$4:$N$4,,,7)
D46	D46	=MATCHES("",$C$4:$N$4,,7,7)
G40	G40	=MATCHES(,$C$4:$N$4,,0,0)
G41	G41	=MATCHES(,$C$4:$N$4,,1,1)
G42	G42	=MATCHES(,$C$4:$N$4,,2,2)
G43	G43	=MATCHES(,$C$4:$N$4,,,3)
G44	G44	=MATCHES(,$C$4:$N$4,,3,3)
G45	G45	=MATCHES(,$C$4:$N$4,,,7)
G46	G46	=MATCHES(,$C$4:$N$4,,7,7)
J40	J40	=MATCHES("",$C$4:$N$4,0,0,0)
J41	J41	=MATCHES("",$C$4:$N$4,0,1,1)
J42	J42	=MATCHES("",$C$4:$N$4,0,2,2)
J43	J43	=MATCHES("",$C$4:$N$4,0,,3)
J44	J44	=MATCHES("",$C$4:$N$4,0,3,3)
J45	J45	=MATCHES("",$C$4:$N$4,0,,7)
J46	J46	=MATCHES("",$C$4:$N$4,0,7,7)
M40	M40	=MATCHES(,$C$4:$N$4,0,0,0)
M41	M41	=MATCHES(,$C$4:$N$4,0,1,1)
M42	M42	=MATCHES(,$C$4:$N$4,0,2,2)
M43	M43	=MATCHES(,$C$4:$N$4,0,,3)
M44	M44	=MATCHES(,$C$4:$N$4,0,3,3)
M45	M45	=MATCHES(,$C$4:$N$4,0,,7)
M46	M46	=MATCHES(,$C$4:$N$4,0,7,7)

Please feel free to share any thoughts or suggestions.

 

[Rnkhch]

Updated code:
1) addresses the issue where the function output is affected by incorrect parameters (i.e. function not crashing to a single #N/A)
2) adds an "output_orientation" parameter that takes three arguments: 0 or omitted, spills the output in the same orientation as the lookup_1D_array; 1, spills the output horizontally; and 2, spills the output vertically
3) adds an "if_empty" parameter that allows for specifying a single output of any desired type (i.e. number, text, blank, logical, or error) if the main function output is empty (i.e. #CALC! with the message "empty arrays are not supported"). (Note that if wrong arguments are entered for any parameter, a single #N/A will be displayed)
4) significantly improves performance speed
5) adopts modular coding allowing for conveniently reusing portions of code with specific functionalities (as I have done for all my other functions)

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

MATCHES

=LAMBDA(lookup_value,lookup_1D_array,[match_type],[output_orientation],[errors],[blanks],[logicals],[if_empty],
   LET(
      v,IF(lookup_value="","",lookup_value),
      a,lookup_1D_array,
      c,COLUMNS(a),
      r,ROWS(a),
      h,IF(c>r,1,0),
      o,PLS(PO0(output_orientation)),
      g,XTYPE(v,3),
      i,SWITCH(g,1,1,2,2,31,3,32,3,41,4,42,4,16),
      j,XTYPE(a,3),
      k,SWITCH(j,1,1,2,2,31,3,32,3,41,4,42,4,16),
      e,IF(ISERROR(MATCH(errors,ErrorNsWA,0)),NA(),errors),
      b,blanks,
      l,logicals,
      f,IFS(
         AND(IO(errors),IO(blanks),IO(logicals)),1,
         AND(IO(blanks),IO(logicals)),2,
         AND(IO(errors),IO(logicals)),3,
         AND(IO(errors),IO(blanks)),4),
      d,IF(
         i=3,
         IFERROR(
            SWITCH(f,
               1,IF(k=3,1,0),
               2,IF(e=-1,IF(k=16,1,0),IFERROR(IF(ERROR.TYPE(a)=e,1,0),0)),
               3,SWITCH(b,0,IF(k=3,1,0),1,IF(j=31,1,0),2,IF(j=32,1,0)),
               4,IF(l=-1,IF(k=4,1,0),SWITCH(l,1,IF(j=41,1,0),0,IF(j=42,1,0)))),
            NA()),
         IF(
            f=1,
            SWITCH(i,
               1,IF(k=1,a,""),
               2,IF(k=2,a,""),
               4,SWITCH(g,
                  41,IF(j=41,1,0),
                  42,IF(j=42,1,0)))
            NA())),
      JAI(
         JAQ(
            FILTER(
               IF(
                  h,
                  SEQUENCE(,c),
                  SEQUENCE(r,)),
               LET(
                  m,PO0(match_type),
                  SWITCH(i,
                     1,ISNUMBER(d)*SWITCH(m,1,d>v,2,d>=v,-1,d<v,-2,d<=v,0,d=v),
                     2,SWITCH(m,0,(IF(JAM(d)=2,1,0))*(d=v)),
                     3,SWITCH(m,0,SWITCH(f,1,d,2,IF(v="",d),3,d,4,IF(v="",d))),
                     4,SWITCH(m,0,IFS(v=TRUE,d,v=FALSE,d))))),
            h,
            o),
         if_empty)
   )
)

XERROR.TYPE (posted separately)

XTYPE (posted separately)

ErrorNsWA (Returns an array containing -1 and all Excel error numbers (0-14))

={-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}

IO (shortened form of ISOMITTED)

=LAMBDA(parameter,ISOMITTED(parameter))

JAI (module for "if_empty")

=LAMBDA(operation,if_empty,
   IF(
      JAN(if_empty),
      NA(),
      LET(
         e,IF(ISERROR(operation),IF(ERROR.TYPE(operation)=14,1,0),0),
         f,IF(ISERROR(ROWS(operation)*COLUMNS(operation)),1,0),
         IF(
            e+f=2,
            IF(
               IO(if_empty),
               operation,
               if_empty),
            operation)
      )
   )
)

JAM (Returns the type of data in a cell or array element [basic form of XTYPE])

=LAMBDA(reference,
   LET(
      r,reference,
      IF(ISERROR(r),16,IF(r="",3,IF(ISNUMBER(r),1,IF(ISTEXT(r),2,IF(OR(TEXT(IFERROR(r,""),10)="TRUE",TEXT(IFERROR(r,""),10)="FALSE"),4,5)))))
   )
)

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

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

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

JAQ ("output_orientation" module for functions that accept 1D references)

=LAMBDA(expression,hv,output_orientation,
   SWITCH(output_orientation,
      0,expression,
      1,IF(hv,expression,TRANSPOSE(expression)),
      2,IF(hv,TRANSPOSE(expression),expression))
)

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 the incorrect parameter is 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))

matches2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		#CALC!																basic types
3		data type:	number	number	logical (false)	number	text	(real) blank	error	logical (true)	error	text	(formula) blank	number				1	dt:NUMBER
4		cells:	1	2	FALSE	-2	&		#DIV/0!	TRUE	#REF!	b		6				2	dt:TEXT
5		position:	1	2	3	4	5	6	7	8	9	10	11	12				3	dt:BLANK
6		XTYPE($C$4:$N$4,2):	1	1	4	1	2	3	16	4	16	2	3	1				4	dt:LOGICAL
7		XTYPE($C$4:$N$4,3):	1	1	42	1	2	31	162	41	164	2	32	1				16	dt:ERROR
8
9																		advanced types
10		match_type	existing number lookup						match_type	nonexisting number lookup								1	dt:NUMBER
11		none	1						none	#CALC!								2	dt:TEXT
12		0	1						0	#CALC!								31	dt:BLANK_REAL
13		1	2	12					1	12								32	dt:BLANK_FORMULA
14		2	1	2	12				2	12								41	dt:LOGICAL_TRUE
15		-1	4						-1	1	2	4						42	dt:LOGICAL_FALSE
16		-2	1	4					-2	1	2	4						160	dt:ERROR_EXTERNAL!
17		(wrong argument) -4	#N/A						(wrong argument) 3	#N/A								161	dt:ERROR_NULL!
18																		162	dt:ERROR_DIV/0!
19		match_type	existing text lookup						match_type	nonexisting text lookup								163	dt:ERROR_VALUE!
20		none	10						none	#CALC!								164	dt:ERROR_REF!
21		0	10						0	#CALC!								165	dt:ERROR_NAME?
22		1	#N/A						1	#N/A								166	dt:ERROR_NUM!
23		2	#N/A						2	#N/A								167	dt:ERROR_N/A!
24		-1	#N/A						-1	#N/A								168	dt:ERROR_GETTING_DATA!
25		-2	#N/A						-2	#N/A								169	dt:ERROR_SPILL!
26																		1610	dt:ERROR_CONNECT!
27		match_type	all blanks lookup (method 1)			match_type	all blanks lookup (method 2)			match_type	all blanks lookup (method 3)			match_type	all blanks lookup (method 4)			1611	dt:ERROR_BLOCKED!
28		none	6	11		none	6	11		none	6	11		none	6	11		1612	dt:ERROR_UNKNOWN!
29		0	6	11		0	6	11		0	6	11		0	6	11		1613	dt:ERROR_FIELD!
30		1	#N/A			1	#N/A			1	#N/A			1	#N/A			1614	dt:ERROR_CALC!
31		2	#N/A			2	#N/A			2	#N/A			2	#N/A
32		-1	#N/A			-1	#N/A			-1	#N/A			-1	#N/A
33		-2	#N/A			-2	#N/A			-2	#N/A			-2	#N/A
34						match_type	Specific blanks lookup (method 1)							match_type	Specific blanks lookup (method 2)
35						none	6							none	6
36						0	6							0	6
37						1	#N/A							1	#N/A
38						2	#N/A							2	#N/A
39						-1	#N/A							-1	#N/A
40						-2	#N/A							-2	#N/A
41						none	11							none	11
42						0	11							0	11
43						1	#N/A							1	#N/A
44						2	#N/A							2	#N/A
45						-1	#N/A							-1	#N/A
46						-2	#N/A							-2	#N/A
47
48		match_type	all errors lookup (method 1)						match_type	all errors lookup (method 2)
49		none	7	9					none	7	9
50		0	7	9					0	7	9
51		1	#N/A						1	#N/A
52		2	#N/A						2	#N/A
53		-1	#N/A						-1	#N/A
54		-2	#N/A						-2	#N/A
55		match_type	specific errors lookup (method 1)			match_type	specific errors lookup (method 2)			match_type	specific errors lookup (method 3)			match_type	specific errors lookup (method 4)
56		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
57		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
58		none	7			0	7			none	7			0	7
59		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
60		none	9			0	9			none	9			0	9
61		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
62		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
63		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
64		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
65		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
66		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
67		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
68		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
69		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
70		none	#CALC!			0	#CALC!			none	#CALC!			0	#CALC!
71		if_empty 12345	12345			if_empty "book"	book			if_empty "" (blank)				if_empty #DIV/0! (error)	#DIV/0!
72		if_empty TRUE	TRUE			if_empty FALSE	FALSE			if_empty {1,2} array	#N/A			if_empty {1,"test"} array	#N/A
73
74		match_type	TRUE logicals lookup (method 1)			match_type	TRUE logicals lookup (method 2)			match_type	TRUE logicals lookup (method 3)			match_type	all logicals lookup (method 1)
75		none	8			none	8			none	8			none	3	8
76		0	8			0	8			0	8			0	3	8
77		1	#N/A			1	#N/A			1	#N/A			1	#N/A
78		2	#N/A			2	#N/A			2	#N/A			2	#N/A
79		-1	#N/A			-1	#N/A			-1	#N/A			-1	#N/A
80		-2	#N/A			-2	#N/A			-2	#N/A			-2	#N/A
81		match_type	FALSE logicals lookup (method 1)			match_type	FALSE logicals lookup (method 2)			match_type	FALSE logicals lookup (method 3)			match_type	all logicals lookup (method 2)
82		none	3			none	3			none	3			none	3	8
83		0	3			0	3			0	3			0	3	8
84		1	#N/A			1	#N/A			1	#N/A			1	#N/A
85		2	#N/A			2	#N/A			2	#N/A			2	#N/A
86		-1	#N/A			-1	#N/A			-1	#N/A			-1	#N/A
87		-2	#N/A			-2	#N/A			-2	#N/A			-2	#N/A
88
89		output_orientation						output_orientation
90		omitted	1	2	12			omitted	0	2	1
91		0	1	2	12			1	1	1	1	2	12
92		1	1	2	12			2	2	2
93		2	1					12	12	12
94			2
95			12
96
correct_params_horizontal

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(lookup_value,lookup_1D_array,[match_type],[output_orientation],[errors],[blanks],[logicals],[if_empty],LET(v,IF(lookup_value="","",lookup_value),a,lookup_1D_array,c,COLUMNS(a),r,ROWS(a),h,IF(c>r,1,0),o,PLS(PO0(output_orientation)),g,XTYPE(v,3),i,SWITCH(g,1,1,2,2,31,3,32,3,41,4,42,4,16),j,XTYPE(a,3),k,SWITCH(j,1,1,2,2,31,3,32,3,41,4,42,4,16),e,IF(ISERROR(MATCH(errors,ErrorNsWA,0)),NA(),errors),b,blanks,l,logicals,f,IFS(AND(IO(errors),IO(blanks),IO(logicals)),1,AND(IO(blanks),IO(logicals)),2,AND(IO(errors),IO(logicals)),3,AND(IO(errors),IO(blanks)),4),d,IF(i=3,IFERROR(SWITCH(f,1,IF(k=3,1,0),2,IF(e=-1,IF(k=16,1,0),IFERROR(IF(ERROR.TYPE(a)=e,1,0),0)),3,SWITCH(b,0,IF(k=3,1,0),1,IF(j=31,1,0),2,IF(j=32,1,0)),4,IF(l=-1,IF(k=4,1,0),SWITCH(l,1,IF(j=41,1,0),0,IF(j=42,1,0)))),NA()),IF(f=1,SWITCH(i,1,IF(k=1,a,""),2,IF(k=2,a,""),4,SWITCH(g,41,IF(j=41,1,0),42,IF(j=42,1,0))),NA())),JAI(JAQ(FILTER(IF(h,SEQUENCE(,c),SEQUENCE(r,)),LET(m,PO0(match_type),SWITCH(i,1,ISNUMBER(d)*SWITCH(m,1,d>v,2,d>=v,-1,d<v,-2,d<=v,0,d=v),2,SWITCH(m,0,(IF(JAM(d)=2,1,0))*(d=v)),3,SWITCH(m,0,SWITCH(f,1,d,2,IF(v="",d),3,d,4,IF(v="",d))),4,SWITCH(m,0,IFS(v=TRUE,d,v=FALSE,d))))),h,o),if_empty)))
I4	I4	=1/0
K4	K4	=LOOKUP(#REF!,#REF!)
M4	M4	=IF(M1>2,1,"")
C6:N6	C6	=XTYPE($C$4:$N$4,2)
C7:N7	C7	=XTYPE($C$4:$N$4,3)
P14	P14	=LET(x,"",TEXT(x,10))
C11	C11	=MATCHES(1,$C$4:$N$4)
C12	C12	=MATCHES(1,$C$4:$N$4,0)
C13:D13	C13	=MATCHES(1,$C$4:$N$4,1)
C14:E14	C14	=MATCHES(1,$C$4:$N$4,2)
C15	C15	=MATCHES(1,$C$4:$N$4,-1)
C16:D16	C16	=MATCHES(1,$C$4:$N$4,-2)
C17	C17	=MATCHES(1,$C$4:$N$4,-4)
J11	J11	=MATCHES(5,$C$4:$N$4)
J12	J12	=MATCHES(5,$C$4:$N$4,0)
J13	J13	=MATCHES(5,$C$4:$N$4,1)
J14	J14	=MATCHES(5,$C$4:$N$4,2)
J15:L15	J15	=MATCHES(5,$C$4:$N$4,-1)
J16:L16	J16	=MATCHES(5,$C$4:$N$4,-2)
J17	J17	=MATCHES(5,$C$4:$N$4,3)
C20	C20	=MATCHES("b",$C$4:$N$4)
C21	C21	=MATCHES("b",$C$4:$N$4,0)
C22	C22	=MATCHES("b",$C$4:$N$4,1)
C23	C23	=MATCHES("b",$C$4:$N$4,2)
C24	C24	=MATCHES("b",$C$4:$N$4,-1)
C25	C25	=MATCHES("b",$C$4:$N$4,-2)
J20	J20	=MATCHES("app",$C$4:$N$4)
J21	J21	=MATCHES("app",$C$4:$N$4,0)
J22	J22	=MATCHES("app",$C$4:$N$4,1)
J23	J23	=MATCHES("app",$C$4:$N$4,2)
J24	J24	=MATCHES("app",$C$4:$N$4,-1)
J25	J25	=MATCHES("app",$C$4:$N$4,-2)
C28:D28	C28	=MATCHES(,$C$4:$N$4,)
C29:D29	C29	=MATCHES(,$C$4:$N$4,0)
C30	C30	=MATCHES(,$C$4:$N$4,1)
C31	C31	=MATCHES(,$C$4:$N$4,2)
C32	C32	=MATCHES(,$C$4:$N$4,-1)
C33	C33	=MATCHES(,$C$4:$N$4,-2)
G28:H28	G28	=MATCHES(,$C$4:$N$4,,,,0)
G29:H29	G29	=MATCHES(,$C$4:$N$4,0,,,0)
G30	G30	=MATCHES(,$C$4:$N$4,1,,,0)
G31	G31	=MATCHES(,$C$4:$N$4,2,,,0)
G32	G32	=MATCHES(,$C$4:$N$4,-1,,,0)
G33	G33	=MATCHES(,$C$4:$N$4,-2,,,0)
K28:L28	K28	=MATCHES("",$C$4:$N$4,)
K29:L29	K29	=MATCHES("",$C$4:$N$4,0)
K30	K30	=MATCHES("",$C$4:$N$4,1)
K31	K31	=MATCHES("",$C$4:$N$4,2)
K32	K32	=MATCHES("",$C$4:$N$4,-1)
K33	K33	=MATCHES("",$C$4:$N$4,-2)
O28:P28	O28	=MATCHES("",$C$4:$N$4,,,,0)
O29:P29	O29	=MATCHES("",$C$4:$N$4,0,,,0)
O30	O30	=MATCHES("",$C$4:$N$4,1,,,0)
O31	O31	=MATCHES("",$C$4:$N$4,2,,,0)
O32	O32	=MATCHES("",$C$4:$N$4,-1,,,0)
O33	O33	=MATCHES("",$C$4:$N$4,-2,,,0)
G35	G35	=MATCHES(,$C$4:$N$4,,,,1)
G36	G36	=MATCHES(,$C$4:$N$4,0,,,1)
G37	G37	=MATCHES(,$C$4:$N$4,1,,,1)
G38	G38	=MATCHES(,$C$4:$N$4,2,,,1)
G39	G39	=MATCHES(,$C$4:$N$4,-1,,,1)
G40	G40	=MATCHES(,$C$4:$N$4,-2,,,1)
G41	G41	=MATCHES(,$C$4:$N$4,,,,2)
G42	G42	=MATCHES(,$C$4:$N$4,0,,,2)
G43	G43	=MATCHES(,$C$4:$N$4,1,,,2)
G44	G44	=MATCHES(,$C$4:$N$4,2,,,2)
G45	G45	=MATCHES(,$C$4:$N$4,-1,,,2)
G46	G46	=MATCHES(,$C$4:$N$4,-2,,,2)
O35	O35	=MATCHES("",$C$4:$N$4,,,,1)
O36	O36	=MATCHES("",$C$4:$N$4,0,,,1)
O37	O37	=MATCHES("",$C$4:$N$4,1,,,1)
O38	O38	=MATCHES("",$C$4:$N$4,2,,,1)
O39	O39	=MATCHES("",$C$4:$N$4,-1,,,1)
O40	O40	=MATCHES("",$C$4:$N$4,-2,,,1)
O41	O41	=MATCHES("",$C$4:$N$4,,,,2)
O42	O42	=MATCHES("",$C$4:$N$4,0,,,2)
O43	O43	=MATCHES("",$C$4:$N$4,1,,,2)
O44	O44	=MATCHES("",$C$4:$N$4,2,,,2)
O45	O45	=MATCHES("",$C$4:$N$4,-1,,,2)
O46	O46	=MATCHES("",$C$4:$N$4,-2,,,2)
C49:D49	C49	=MATCHES(,$C$4:$N$4,,,-1)
C50:D50	C50	=MATCHES(,$C$4:$N$4,0,,-1)
C51	C51	=MATCHES(,$C$4:$N$4,1,,-1)
C52	C52	=MATCHES(,$C$4:$N$4,2,,-1)
C53	C53	=MATCHES(,$C$4:$N$4,-1,,-1)
C54	C54	=MATCHES(,$C$4:$N$4,-2,,-1)
J49:K49	J49	=MATCHES("",$C$4:$N$4,,,-1)
J50:K50	J50	=MATCHES("",$C$4:$N$4,0,,-1)
J51	J51	=MATCHES("",$C$4:$N$4,1,,-1)
J52	J52	=MATCHES("",$C$4:$N$4,2,,-1)
J53	J53	=MATCHES("",$C$4:$N$4,-1,,-1)
J54	J54	=MATCHES("",$C$4:$N$4,-2,,-1)
C56	C56	=MATCHES(,$C$4:$N$4,,,0)
C57	C57	=MATCHES(,$C$4:$N$4,,,1)
C58	C58	=MATCHES(,$C$4:$N$4,,,2)
C59	C59	=MATCHES(,$C$4:$N$4,,,3)
C60	C60	=MATCHES(,$C$4:$N$4,,,4)
C61	C61	=MATCHES(,$C$4:$N$4,,,5)
C62	C62	=MATCHES(,$C$4:$N$4,,,6)
C63	C63	=MATCHES(,$C$4:$N$4,,,7)
C64	C64	=MATCHES(,$C$4:$N$4,,,8)
C65	C65	=MATCHES(,$C$4:$N$4,,,9)
C66	C66	=MATCHES(,$C$4:$N$4,,,10)
C67	C67	=MATCHES(,$C$4:$N$4,,,11)
C68	C68	=MATCHES(,$C$4:$N$4,,,12)
C69	C69	=MATCHES(,$C$4:$N$4,,,13)
C70	C70	=MATCHES(,$C$4:$N$4,,,14)
C71	C71	=MATCHES(,$C$4:$N$4,,,14,,,12345)
C72	C72	=MATCHES(,$C$4:$N$4,,,14,,,TRUE)
G56:G57	G56	=MATCHES(,$C$4:$N$4,0,,1)
G58	G58	=MATCHES(,$C$4:$N$4,0,,2)
G59	G59	=MATCHES(,$C$4:$N$4,0,,3)
G60	G60	=MATCHES(,$C$4:$N$4,0,,4)
G61	G61	=MATCHES(,$C$4:$N$4,0,,5)
G62	G62	=MATCHES(,$C$4:$N$4,0,,6)
G63	G63	=MATCHES(,$C$4:$N$4,0,,7)
G64	G64	=MATCHES(,$C$4:$N$4,0,,8)
G65	G65	=MATCHES(,$C$4:$N$4,0,,9)
G66	G66	=MATCHES(,$C$4:$N$4,0,,10)
G67	G67	=MATCHES(,$C$4:$N$4,0,,11)
G68	G68	=MATCHES(,$C$4:$N$4,0,,12)
G69	G69	=MATCHES(,$C$4:$N$4,0,,13)
G70	G70	=MATCHES(,$C$4:$N$4,0,,14)
G71	G71	=MATCHES(,$C$4:$N$4,0,,14,,,"book")
G72	G72	=MATCHES(,$C$4:$N$4,,,14,,,FALSE)
K56:K57	K56	=MATCHES("",$C$4:$N$4,,,1)
K58	K58	=MATCHES("",$C$4:$N$4,,,2)
K59	K59	=MATCHES("",$C$4:$N$4,,,3)
K60	K60	=MATCHES("",$C$4:$N$4,,,4)
K61	K61	=MATCHES("",$C$4:$N$4,,,5)
K62	K62	=MATCHES("",$C$4:$N$4,,,6)
K63	K63	=MATCHES("",$C$4:$N$4,,,7)
K64	K64	=MATCHES("",$C$4:$N$4,,,8)
K65	K65	=MATCHES("",$C$4:$N$4,,,9)
K66	K66	=MATCHES("",$C$4:$N$4,,,10)
K67	K67	=MATCHES("",$C$4:$N$4,,,11)
K68	K68	=MATCHES("",$C$4:$N$4,,,12)
K69	K69	=MATCHES("",$C$4:$N$4,,,13)
K70	K70	=MATCHES("",$C$4:$N$4,,,14)
K71	K71	=MATCHES("",$C$4:$N$4,,,14,,,"")
K72	K72	=MATCHES(,$C$4:$N$4,,,14,,,{1,2})
O56:O57	O56	=MATCHES("",$C$4:$N$4,0,,1)
O58	O58	=MATCHES("",$C$4:$N$4,0,,2)
O59	O59	=MATCHES("",$C$4:$N$4,0,,3)
O60	O60	=MATCHES("",$C$4:$N$4,0,,4)
O61	O61	=MATCHES("",$C$4:$N$4,0,,5)
O62	O62	=MATCHES("",$C$4:$N$4,0,,6)
O63	O63	=MATCHES("",$C$4:$N$4,0,,7)
O64	O64	=MATCHES("",$C$4:$N$4,0,,8)
O65	O65	=MATCHES("",$C$4:$N$4,0,,9)
O66	O66	=MATCHES("",$C$4:$N$4,0,,10)
O67	O67	=MATCHES("",$C$4:$N$4,0,,11)
O68	O68	=MATCHES("",$C$4:$N$4,0,,12)
O69	O69	=MATCHES("",$C$4:$N$4,0,,13)
O70	O70	=MATCHES("",$C$4:$N$4,0,,14)
O71	O71	=MATCHES("",$C$4:$N$4,0,,14,,,#DIV/0!)
O72	O72	=MATCHES(,$C$4:$N$4,,,14,,,{1,"test"})
C75	C75	=MATCHES(TRUE,$C$4:$N$4)
C76	C76	=MATCHES(TRUE,$C$4:$N$4,0)
C77	C77	=MATCHES(TRUE,$C$4:$N$4,1)
C78	C78	=MATCHES(TRUE,$C$4:$N$4,2)
C79	C79	=MATCHES(TRUE,$C$4:$N$4,-1)
C80	C80	=MATCHES(TRUE,$C$4:$N$4,-2)
G75	G75	=MATCHES(,$C$4:$N$4,,,,,1)
G76	G76	=MATCHES(,$C$4:$N$4,0,,,,1)
G77	G77	=MATCHES(,$C$4:$N$4,1,,,,1)
G78	G78	=MATCHES(,$C$4:$N$4,2,,,,1)
G79	G79	=MATCHES(,$C$4:$N$4,-1,,,,1)
G80	G80	=MATCHES(,$C$4:$N$4,-2,,,,1)
K75	K75	=MATCHES("",$C$4:$N$4,,,,,1)
K76	K76	=MATCHES("",$C$4:$N$4,0,,,,1)
K77	K77	=MATCHES("",$C$4:$N$4,1,,,,1)
K78	K78	=MATCHES("",$C$4:$N$4,2,,,,1)
K79	K79	=MATCHES("",$C$4:$N$4,-1,,,,1)
K80	K80	=MATCHES("",$C$4:$N$4,-2,,,,1)
O75:P75	O75	=MATCHES(,$C$4:$N$4,,,,,-1)
O76:P76	O76	=MATCHES(,$C$4:$N$4,0,,,,-1)
O77	O77	=MATCHES(,$C$4:$N$4,1,,,,-1)
O78	O78	=MATCHES(,$C$4:$N$4,2,,,,-1)
O79	O79	=MATCHES(,$C$4:$N$4,-1,,,,-1)
O80	O80	=MATCHES(,$C$4:$N$4,-2,,,,-1)
C82	C82	=MATCHES(FALSE,$C$4:$N$4)
C83	C83	=MATCHES(FALSE,$C$4:$N$4,0)
C84	C84	=MATCHES(FALSE,$C$4:$N$4,1)
C85	C85	=MATCHES(FALSE,$C$4:$N$4,2)
C86	C86	=MATCHES(FALSE,$C$4:$N$4,-1)
C87	C87	=MATCHES(FALSE,$C$4:$N$4,-2)
G82	G82	=MATCHES(,$C$4:$N$4,,,,,0)
G83	G83	=MATCHES(,$C$4:$N$4,0,,,,0)
G84	G84	=MATCHES(,$C$4:$N$4,1,,,,0)
G85	G85	=MATCHES(,$C$4:$N$4,2,,,,0)
G86	G86	=MATCHES(,$C$4:$N$4,-1,,,,0)
G87	G87	=MATCHES(,$C$4:$N$4,-2,,,,0)
K82	K82	=MATCHES("",$C$4:$N$4,,,,,0)
K83	K83	=MATCHES("",$C$4:$N$4,0,,,,0)
K84	K84	=MATCHES("",$C$4:$N$4,1,,,,0)
K85	K85	=MATCHES("",$C$4:$N$4,2,,,,0)
K86	K86	=MATCHES("",$C$4:$N$4,-1,,,,0)
K87	K87	=MATCHES("",$C$4:$N$4,-2,,,,0)
O82:P82	O82	=MATCHES("",$C$4:$N$4,,,,,-1)
O83:P83	O83	=MATCHES("",$C$4:$N$4,0,,,,-1)
O84	O84	=MATCHES("",$C$4:$N$4,1,,,,-1)
O85	O85	=MATCHES("",$C$4:$N$4,2,,,,-1)
O86	O86	=MATCHES("",$C$4:$N$4,-1,,,,-1)
O87	O87	=MATCHES("",$C$4:$N$4,-2,,,,-1)
H91:H93	H91	=MATCHES(1,TRANSPOSE($C$4:$N$4),2,)
I91:I93	I91	=MATCHES(1,TRANSPOSE($C$4:$N$4),2,0)
J91:J93	J91	=MATCHES(1,TRANSPOSE($C$4:$N$4),2,2)
K91:M91	K91	=MATCHES(1,TRANSPOSE($C$4:$N$4),2,1)
C90:E90	C90	=MATCHES(1,$C$4:$N$4,2,)
C91:E91	C91	=MATCHES(1,$C$4:$N$4,2,0)
C92:E92	C92	=MATCHES(1,$C$4:$N$4,2,1)
C93:C95	C93	=MATCHES(1,$C$4:$N$4,2,2)
Dynamic array formulas.

 

[steven88]

cannot use

 

[Rnkhch]

Can you show me exactly what you were typing? I just double checked the function in multiple ways, and it's working on my template.

 

[steven88]

i put new lambda for matches it cant save

 

[Rnkhch]

I need to see exactly what syntax you are typing at the point of hitting ENTER, which generates that pop-up error.

On my end, it's ENTERing fine with no issues in several tests I did.

 

[jaeiow]

It's missing a comma.

42,IF(j=42,1,0)))
            NA())),

 

[Rnkhch]

Oh I see, sorry, and thanks much for catching that; it was probably accidentally deleted while I was trying to format the code line-by-line. I didn't realize Steven88 was trying to paste the actual code itself.

It's best to always download my template (through the link in my signature) as I update it very regularly, so most of my functions in the template are more up-to-date/optimized than what I've posted here. This will also avoid any rare typos that may be introduced while creating the forum posts.

I'll eventually post the updates after my students/colleagues use them for a while and don't report any issues.

 

[jaeiow]

You should post as an AFE module. 1) Most people aren't going to download a file, and 2) you have a lot of submodules.

 

[Rnkhch]

I see, that's an excellent idea! Will do that for all my future posts/updates.