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.

 

[steven88]

It's missing a comma.

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

thanks

 

[Rnkhch]

Updated code: adds the ability to find the positions of the two new blank types 33 and 34 (see the latest update for XTYPE)

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,33,3,34,3,41,4,42,4,16),
      j,XTYPE(a,3),
      k,SWITCH(j,1,1,2,2,31,3,32,3,33,3,34,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),
                  3,IF(j=33,1,0),
                  4,IF(j=34,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)
   )
)

XTYPE (posted separately)

XERROR.TYPE (posted separately)

(The code for the remaining helper functions is not changed, so I'm not including them again here.)