MATCHES

MATCHES(lookup_value,lookup_1D_array,[match_type],[errors],[blanks],[logicals])
lookup_value
Required. Specifies the value that is intended to be looked up in an array
lookup_1D_array
Required. Specifies the one-dimensional array that is used for the lookup
match_type
Optional. Specifies the type of matching and takes one of five arguments: 0 or omitted, for equal to; 1 for greater than; 2 for greater than or equal to; -1 for lesser than; and -2 for less than or equal to
errors
Optional. Is used for looking up positions of errors and takes one of fifteen arguments: -1 for 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!
blanks
Optional. Is used for looking up positions of blanks and takes one of three arguments: 0 for all blanks; 1 for real blanks; 2 for formula blanks
logicals
Optional. Is used for looking up positions of logicals and takes one of three arguments: -1 for all logicals; 0 for FALSE; 1 for TRUE
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
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
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.

Excel Formula: 
=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
ABCDEFGHIJKLMNOPQ
1
2#CALC!
3data type:numbernumberlogical (false)numbertext(real) blankerrorlogical (true)errortext(formula) blanknumber
4cells:12FALSE-2&#DIV/0!TRUE#REF!b 6
5position:123456789101112
6XTYPE($C$4:$N$4):dt:NUMBERdt:NUMBERdt:LOGICALdt:NUMBERdt:TEXTdt:BLANKdt:ERRORdt:LOGICALdt:ERRORdt:TEXTdt:BLANKdt:NUMBER
7XTYPE($C$4:$N$4,1):dt:NUMBERdt:NUMBERdt:LOGICAL_FALSEdt:NUMBERdt:TEXTdt:BLANK_REALdt:ERROR_DIV/0!dt:LOGICAL_TRUEdt:ERROR_REF!dt:TEXTdt:BLANK_FORMULAdt:NUMBER
8
9
10match_typeexisting number lookupmatch_typenonexisting number lookup
11none1none#N/A
12010#N/A
131212112
1421212212 
15-14-1124
16-214-2124
17
18match_typeexisting text lookupmatch_typenonexisting text lookup
19none10none#N/A
200100#N/A
211#N/A1#N/A
222#N/A2#N/A
23-1#N/A-1#N/A
24-2#N/A-2#N/A
25
26match_typeall blanks lookup (method 1)match_typeall blanks lookup (method 2)match_typeall blanks lookup (method 3)match_typeall blanks lookup (method 4)
27none611none611none611none611
280611061106110611
291#N/A1#N/A1#N/A1#N/A
302#N/A2#N/A2#N/A2#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
33match_typeSpecific blanks lookup (method 1)match_typeSpecific blanks lookup (method 2)
34none6none6
350606
361#N/A1#N/A
372#N/A2#N/A
38-1#N/A-1#N/A
39-2#N/A-2#N/A
40none11none11
41011011
421#N/A1#N/A
432#N/A2#N/A
44-1#N/A-1#N/A
45-2#N/A-2#N/A
46
47match_typeall errors lookup (method 1)match_typeall errors lookup (method 2)
48none79none79
49079079
501#N/A1#N/A
512#N/A2#N/A
52-1#N/A-1#N/A
53-2#N/A-2#N/A
54match_typespecific errors lookup (method 1)match_typespecific errors lookup (method 2)match_typespecific errors lookup (method 3)match_typespecific errors lookup (method 4)
55none#N/A0#N/Anone#N/A0#N/A
56none#N/A0#N/Anone#N/A0#N/A
57none707none707
58none#N/A0#N/Anone#N/A0#N/A
59none909none909
60none#N/A0#N/Anone#N/A0#N/A
61none#N/A0#N/Anone#N/A0#N/A
62none#N/A0#N/Anone#N/A0#N/A
63none#N/A0#N/Anone#N/A0#N/A
64none#N/A0#N/Anone#N/A0#N/A
65none#N/A0#N/Anone#N/A0#N/A
66none#N/A0#N/Anone#N/A0#N/A
67none#N/A0#N/Anone#N/A0#N/A
68none#N/A0#N/Anone#N/A0#N/A
69none#N/A0#N/Anone#N/A0#N/A
70
71match_typeTRUE logicals lookup (method 1)match_typeTRUE logicals lookup (method 2)match_typeTRUE logicals lookup (method 3)match_typeall logicals lookup (method 1)
72none8none8none8none38
73080808038
741#N/A1#N/A1#N/A1#N/A
752#N/A2#N/A2#N/A2#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
78match_typeFALSE logicals lookup (method 1)match_typeFALSE logicals lookup (method 2)match_typeFALSE logicals lookup (method 3)match_typeall logicals lookup (method 2)
79none3none3none3none38
80030303038
811#N/A1#N/A1#N/A1#N/A
822#N/A2#N/A2#N/A2#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
RangeFormula
B2B2=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)))
I4I4=1/0
K4K4=LOOKUP(#REF!,#REF!)
M4M4=IF(M1>2,1,"")
C6:N6C6=XTYPE(C4:N4)
C7:N7C7=XTYPE(C4:N4,1)
P14P14=LET(x,"",TEXT(x,10))
C11C11=MATCHES(1,C4:N4)
C12C12=MATCHES(1,C4:N4,0)
C13:D13C13=MATCHES(1,C4:N4,1)
C14:E14C14=MATCHES(1,C4:N4,2)
C15C15=MATCHES(1,C4:N4,-1)
C16:D16C16=MATCHES(1,C4:N4,-2)
J11J11=MATCHES(5,$C$4:$N$4)
J12J12=MATCHES(5,$C$4:$N$4,0)
J13J13=MATCHES(5,$C$4:$N$4,1)
J14J14=MATCHES(5,$C$4:$N$4,2)
J15:L15J15=MATCHES(5,$C$4:$N$4,-1)
J16:L16J16=MATCHES(5,$C$4:$N$4,-2)
C19C19=MATCHES("b",C4:N4)
C20C20=MATCHES("b",C4:N4,0)
C21C21=MATCHES("b",C4:N4,1)
C22C22=MATCHES("b",C4:N4,2)
C23C23=MATCHES("b",C4:N4,-1)
C24C24=MATCHES("b",C4:N4,-2)
J19J19=MATCHES("app",$C$4:$N$4)
J20J20=MATCHES("app",$C$4:$N$4,0)
J21J21=MATCHES("app",$C$4:$N$4,1)
J22J22=MATCHES("app",$C$4:$N$4,2)
J23J23=MATCHES("app",$C$4:$N$4,-1)
J24J24=MATCHES("app",$C$4:$N$4,-2)
C27:D27C27=MATCHES(,$C$4:$N$4,)
C28:D28C28=MATCHES(,$C$4:$N$4,0)
C29C29=MATCHES(,$C$4:$N$4,1)
C30C30=MATCHES(,$C$4:$N$4,2)
C31C31=MATCHES(,$C$4:$N$4,-1)
C32C32=MATCHES(,$C$4:$N$4,-2)
G27:H27G27=MATCHES(,$C$4:$N$4,,,0)
G28:H28G28=MATCHES(,$C$4:$N$4,0,,0)
G29G29=MATCHES(,$C$4:$N$4,1,,0)
G30G30=MATCHES(,$C$4:$N$4,2,,0)
G31G31=MATCHES(,$C$4:$N$4,-1,,0)
G32G32=MATCHES(,$C$4:$N$4,-2,,0)
K27:L27K27=MATCHES("",$C$4:$N$4,)
K28:L28K28=MATCHES("",$C$4:$N$4,0)
K29K29=MATCHES("",$C$4:$N$4,1)
K30K30=MATCHES("",$C$4:$N$4,2)
K31K31=MATCHES("",$C$4:$N$4,-1)
K32K32=MATCHES("",$C$4:$N$4,-2)
O27:P27O27=MATCHES("",$C$4:$N$4,,,0)
O28:P28O28=MATCHES("",$C$4:$N$4,0,,0)
O29O29=MATCHES("",$C$4:$N$4,1,,0)
O30O30=MATCHES("",$C$4:$N$4,2,,0)
O31O31=MATCHES("",$C$4:$N$4,-1,,0)
O32O32=MATCHES("",$C$4:$N$4,-2,,0)
G34G34=MATCHES(,$C$4:$N$4,,,1)
G35G35=MATCHES(,$C$4:$N$4,0,,1)
G36G36=MATCHES(,$C$4:$N$4,1,,1)
G37G37=MATCHES(,$C$4:$N$4,2,,1)
G38G38=MATCHES(,$C$4:$N$4,-1,,1)
G39G39=MATCHES(,$C$4:$N$4,-2,,1)
G40G40=MATCHES(,$C$4:$N$4,,,2)
G41G41=MATCHES(,$C$4:$N$4,0,,2)
G42G42=MATCHES(,$C$4:$N$4,1,,2)
G43G43=MATCHES(,$C$4:$N$4,2,,2)
G44G44=MATCHES(,$C$4:$N$4,-1,,2)
G45G45=MATCHES(,$C$4:$N$4,-2,,2)
O34O34=MATCHES("",$C$4:$N$4,,,1)
O35O35=MATCHES("",$C$4:$N$4,0,,1)
O36O36=MATCHES("",$C$4:$N$4,1,,1)
O37O37=MATCHES("",$C$4:$N$4,2,,1)
O38O38=MATCHES("",$C$4:$N$4,-1,,1)
O39O39=MATCHES("",$C$4:$N$4,-2,,1)
O40O40=MATCHES("",$C$4:$N$4,,,2)
O41O41=MATCHES("",$C$4:$N$4,0,,2)
O42O42=MATCHES("",$C$4:$N$4,1,,2)
O43O43=MATCHES("",$C$4:$N$4,2,,2)
O44O44=MATCHES("",$C$4:$N$4,-1,,2)
O45O45=MATCHES("",$C$4:$N$4,-2,,2)
C48:D48C48=MATCHES(,$C$4:$N$4,,-1)
C49:D49C49=MATCHES(,$C$4:$N$4,0,-1)
C50C50=MATCHES(,$C$4:$N$4,1,-1)
C51C51=MATCHES(,$C$4:$N$4,2,-1)
C52C52=MATCHES(,$C$4:$N$4,-1,-1)
C53C53=MATCHES(,$C$4:$N$4,-2,-1)
J48:K48J48=MATCHES("",$C$4:$N$4,,-1)
J49:K49J49=MATCHES("",$C$4:$N$4,0,-1)
J50J50=MATCHES("",$C$4:$N$4,1,-1)
J51J51=MATCHES("",$C$4:$N$4,2,-1)
J52J52=MATCHES("",$C$4:$N$4,-1,-1)
J53J53=MATCHES("",$C$4:$N$4,-2,-1)
C55C55=MATCHES(,$C$4:$N$4,,0)
C56C56=MATCHES(,$C$4:$N$4,,1)
C57C57=MATCHES(,$C$4:$N$4,,2)
C58C58=MATCHES(,$C$4:$N$4,,3)
C59C59=MATCHES(,$C$4:$N$4,,4)
C60C60=MATCHES(,$C$4:$N$4,,5)
C61C61=MATCHES(,$C$4:$N$4,,6)
C62C62=MATCHES(,$C$4:$N$4,,7)
C63C63=MATCHES(,$C$4:$N$4,,8)
C64C64=MATCHES(,$C$4:$N$4,,9)
C65C65=MATCHES(,$C$4:$N$4,,10)
C66C66=MATCHES(,$C$4:$N$4,,11)
C67C67=MATCHES(,$C$4:$N$4,,12)
C68C68=MATCHES(,$C$4:$N$4,,13)
C69C69=MATCHES(,$C$4:$N$4,,14)
G55:G56G55=MATCHES(,$C$4:$N$4,0,1)
G57G57=MATCHES(,$C$4:$N$4,0,2)
G58G58=MATCHES(,$C$4:$N$4,0,3)
G59G59=MATCHES(,$C$4:$N$4,0,4)
G60G60=MATCHES(,$C$4:$N$4,0,5)
G61G61=MATCHES(,$C$4:$N$4,0,6)
G62G62=MATCHES(,$C$4:$N$4,0,7)
G63G63=MATCHES(,$C$4:$N$4,0,8)
G64G64=MATCHES(,$C$4:$N$4,0,9)
G65G65=MATCHES(,$C$4:$N$4,0,10)
G66G66=MATCHES(,$C$4:$N$4,0,11)
G67G67=MATCHES(,$C$4:$N$4,0,12)
G68G68=MATCHES(,$C$4:$N$4,0,13)
G69G69=MATCHES(,$C$4:$N$4,0,14)
K55:K56K55=MATCHES("",$C$4:$N$4,,1)
K57K57=MATCHES("",$C$4:$N$4,,2)
K58K58=MATCHES("",$C$4:$N$4,,3)
K59K59=MATCHES("",$C$4:$N$4,,4)
K60K60=MATCHES("",$C$4:$N$4,,5)
K61K61=MATCHES("",$C$4:$N$4,,6)
K62K62=MATCHES("",$C$4:$N$4,,7)
K63K63=MATCHES("",$C$4:$N$4,,8)
K64K64=MATCHES("",$C$4:$N$4,,9)
K65K65=MATCHES("",$C$4:$N$4,,10)
K66K66=MATCHES("",$C$4:$N$4,,11)
K67K67=MATCHES("",$C$4:$N$4,,12)
K68K68=MATCHES("",$C$4:$N$4,,13)
K69K69=MATCHES("",$C$4:$N$4,,14)
O55:O56O55=MATCHES("",$C$4:$N$4,0,1)
O57O57=MATCHES("",$C$4:$N$4,0,2)
O58O58=MATCHES("",$C$4:$N$4,0,3)
O59O59=MATCHES("",$C$4:$N$4,0,4)
O60O60=MATCHES("",$C$4:$N$4,0,5)
O61O61=MATCHES("",$C$4:$N$4,0,6)
O62O62=MATCHES("",$C$4:$N$4,0,7)
O63O63=MATCHES("",$C$4:$N$4,0,8)
O64O64=MATCHES("",$C$4:$N$4,0,9)
O65O65=MATCHES("",$C$4:$N$4,0,10)
O66O66=MATCHES("",$C$4:$N$4,0,11)
O67O67=MATCHES("",$C$4:$N$4,0,12)
O68O68=MATCHES("",$C$4:$N$4,0,13)
O69O69=MATCHES("",$C$4:$N$4,0,14)
C72C72=MATCHES(TRUE,$C$4:$N$4)
C73C73=MATCHES(TRUE,$C$4:$N$4,0)
C74C74=MATCHES(TRUE,$C$4:$N$4,1)
C75C75=MATCHES(TRUE,$C$4:$N$4,2)
C76C76=MATCHES(TRUE,$C$4:$N$4,-1)
C77C77=MATCHES(TRUE,$C$4:$N$4,-2)
G72G72=MATCHES(,$C$4:$N$4,,,,1)
G73G73=MATCHES(,$C$4:$N$4,0,,,1)
G74G74=MATCHES(,$C$4:$N$4,1,,,1)
G75G75=MATCHES(,$C$4:$N$4,2,,,1)
G76G76=MATCHES(,$C$4:$N$4,-1,,,1)
G77G77=MATCHES(,$C$4:$N$4,-2,,,1)
K72K72=MATCHES("",$C$4:$N$4,,,,1)
K73K73=MATCHES("",$C$4:$N$4,0,,,1)
K74K74=MATCHES("",$C$4:$N$4,1,,,1)
K75K75=MATCHES("",$C$4:$N$4,2,,,1)
K76K76=MATCHES("",$C$4:$N$4,-1,,,1)
K77K77=MATCHES("",$C$4:$N$4,-2,,,1)
O72:P72O72=MATCHES(,$C$4:$N$4,,,,-1)
O73:P73O73=MATCHES(,$C$4:$N$4,0,,,-1)
O74O74=MATCHES(,$C$4:$N$4,1,,,-1)
O75O75=MATCHES(,$C$4:$N$4,2,,,-1)
O76O76=MATCHES(,$C$4:$N$4,-1,,,-1)
O77O77=MATCHES(,$C$4:$N$4,-2,,,-1)
C79C79=MATCHES(FALSE,$C$4:$N$4)
C80C80=MATCHES(FALSE,$C$4:$N$4,0)
C81C81=MATCHES(FALSE,$C$4:$N$4,1)
C82C82=MATCHES(FALSE,$C$4:$N$4,2)
C83C83=MATCHES(FALSE,$C$4:$N$4,-1)
C84C84=MATCHES(FALSE,$C$4:$N$4,-2)
G79G79=MATCHES(,$C$4:$N$4,,,,0)
G80G80=MATCHES(,$C$4:$N$4,0,,,0)
G81G81=MATCHES(,$C$4:$N$4,1,,,0)
G82G82=MATCHES(,$C$4:$N$4,2,,,0)
G83G83=MATCHES(,$C$4:$N$4,-1,,,0)
G84G84=MATCHES(,$C$4:$N$4,-2,,,0)
K79K79=MATCHES("",$C$4:$N$4,,,,0)
K80K80=MATCHES("",$C$4:$N$4,0,,,0)
K81K81=MATCHES("",$C$4:$N$4,1,,,0)
K82K82=MATCHES("",$C$4:$N$4,2,,,0)
K83K83=MATCHES("",$C$4:$N$4,-1,,,0)
K84K84=MATCHES("",$C$4:$N$4,-2,,,0)
O79:P79O79=MATCHES("",$C$4:$N$4,,,,-1)
O80:P80O80=MATCHES("",$C$4:$N$4,0,,,-1)
O81O81=MATCHES("",$C$4:$N$4,1,,,-1)
O82O82=MATCHES("",$C$4:$N$4,2,,,-1)
O83O83=MATCHES("",$C$4:$N$4,-1,,,-1)
O84O84=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
ABCDEFGHIJKLMNO
1
2#CALC!
3type:numbernumberlogical (false)numbertext(real) blankerrorlogical (true)errortext(formula) blanknumber
4cells:12FALSE-2&#DIV/0!TRUE#N/Ab 6
5position:123456789101112
6
7
8wrong paramsexistingwrong paramsnonexistingwrong paramsexistingwrong paramsnonexisting
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
39other
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
RangeFormula
B2B2=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)))
I4I4=1/0
K4K4=LOOKUP(D2,F2:G2)
M4M4=IF(K2>2,1,"")
D9D9=MATCHES(1,$C$4:$N$4,,0,)
D10D10=MATCHES(1,$C$4:$N$4,,,0)
D11D11=MATCHES(1,$C$4:$N$4,,0,0)
D12D12=MATCHES(1,$C$4:$N$4,,1,)
D13D13=MATCHES(1,$C$4:$N$4,,,1)
D14D14=MATCHES(1,$C$4:$N$4,,1,1)
D15D15=MATCHES(1,$C$4:$N$4,,2,)
D16D16=MATCHES(1,$C$4:$N$4,,,2)
D17D17=MATCHES(1,$C$4:$N$4,,2,2)
D18D18=MATCHES(1,$C$4:$N$4,,3,)
D19D19=MATCHES(1,$C$4:$N$4,,,3)
D20D20=MATCHES(1,$C$4:$N$4,,3,3)
D21D21=MATCHES(1,$C$4:$N$4,,7,)
D22D22=MATCHES(1,$C$4:$N$4,,,7)
D23D23=MATCHES(1,$C$4:$N$4,,7,7)
D24D24=MATCHES("b",$C$4:$N$4,,0,)
D25D25=MATCHES("b",$C$4:$N$4,,,0)
D26D26=MATCHES("b",$C$4:$N$4,,0,0)
D27D27=MATCHES("b",$C$4:$N$4,,1,)
D28D28=MATCHES("b",$C$4:$N$4,,,1)
D29D29=MATCHES("b",$C$4:$N$4,,1,1)
D30D30=MATCHES("b",$C$4:$N$4,,2,)
D31D31=MATCHES("b",$C$4:$N$4,,,2)
D32D32=MATCHES("b",$C$4:$N$4,,2,2)
D33D33=MATCHES("b",$C$4:$N$4,,3,)
D34D34=MATCHES("b",$C$4:$N$4,,,3)
D35D35=MATCHES("b",$C$4:$N$4,,3,3)
D36D36=MATCHES("b",$C$4:$N$4,,7,)
D37D37=MATCHES("b",$C$4:$N$4,,,7)
D38D38=MATCHES("b",$C$4:$N$4,,7,7)
G9G9=MATCHES(4,$C$4:$N$4,,0,)
G10G10=MATCHES(4,$C$4:$N$4,,,0)
G11G11=MATCHES(4,$C$4:$N$4,,0,0)
G12G12=MATCHES(4,$C$4:$N$4,,1,)
G13G13=MATCHES(4,$C$4:$N$4,,,1)
G14G14=MATCHES(4,$C$4:$N$4,,1,1)
G15G15=MATCHES(4,$C$4:$N$4,,2,)
G16G16=MATCHES(4,$C$4:$N$4,,,2)
G17G17=MATCHES(4,$C$4:$N$4,,2,2)
G18G18=MATCHES(4,$C$4:$N$4,,3,)
G19G19=MATCHES(4,$C$4:$N$4,,,3)
G20G20=MATCHES(4,$C$4:$N$4,,3,3)
G21G21=MATCHES(4,$C$4:$N$4,,7,)
G22G22=MATCHES(4,$C$4:$N$4,,,7)
G23G23=MATCHES(4,$C$4:$N$4,,7,7)
G24G24=MATCHES("a",$C$4:$N$4,,0,)
G25G25=MATCHES("a",$C$4:$N$4,,,0)
G26G26=MATCHES("a",$C$4:$N$4,,0,0)
G27G27=MATCHES("a",$C$4:$N$4,,1,)
G28G28=MATCHES("a",$C$4:$N$4,,,1)
G29G29=MATCHES("a",$C$4:$N$4,,1,1)
G30G30=MATCHES("a",$C$4:$N$4,,2,)
G31G31=MATCHES("a",$C$4:$N$4,,,2)
G32G32=MATCHES("a",$C$4:$N$4,,2,2)
G33G33=MATCHES("a",$C$4:$N$4,,3,)
G34G34=MATCHES("a",$C$4:$N$4,,,3)
G35G35=MATCHES("a",$C$4:$N$4,,3,3)
G36G36=MATCHES("a",$C$4:$N$4,,7,)
G37G37=MATCHES("a",$C$4:$N$4,,,7)
G38G38=MATCHES("a",$C$4:$N$4,,7,7)
J9J9=MATCHES(1,$C$4:$N$4,0,0,)
J10J10=MATCHES(1,$C$4:$N$4,0,,0)
J11J11=MATCHES(1,$C$4:$N$4,0,0,0)
J12J12=MATCHES(1,$C$4:$N$4,0,1,)
J13J13=MATCHES(1,$C$4:$N$4,0,,1)
J14J14=MATCHES(1,$C$4:$N$4,0,1,1)
J15J15=MATCHES(1,$C$4:$N$4,0,2,)
J16J16=MATCHES(1,$C$4:$N$4,0,,2)
J17J17=MATCHES(1,$C$4:$N$4,0,2,2)
J18J18=MATCHES(1,$C$4:$N$4,0,3,)
J19J19=MATCHES(1,$C$4:$N$4,0,,3)
J20J20=MATCHES(1,$C$4:$N$4,0,3,3)
J21J21=MATCHES(1,$C$4:$N$4,0,7,)
J22J22=MATCHES(1,$C$4:$N$4,0,,7)
J23J23=MATCHES(1,$C$4:$N$4,0,7,7)
J24J24=MATCHES("b",$C$4:$N$4,0,0,)
J25J25=MATCHES("b",$C$4:$N$4,0,,0)
J26J26=MATCHES("b",$C$4:$N$4,0,0,0)
J27J27=MATCHES("b",$C$4:$N$4,0,1,)
J28J28=MATCHES("b",$C$4:$N$4,0,,1)
J29J29=MATCHES("b",$C$4:$N$4,0,1,1)
J30J30=MATCHES("b",$C$4:$N$4,0,2,)
J31J31=MATCHES("b",$C$4:$N$4,0,,2)
J32J32=MATCHES("b",$C$4:$N$4,0,2,2)
J33J33=MATCHES("b",$C$4:$N$4,0,3,)
J34J34=MATCHES("b",$C$4:$N$4,0,,3)
J35J35=MATCHES("b",$C$4:$N$4,0,3,3)
J36J36=MATCHES("b",$C$4:$N$4,0,7,)
J37J37=MATCHES("b",$C$4:$N$4,0,,7)
J38J38=MATCHES("b",$C$4:$N$4,0,7,7)
M9M9=MATCHES(4,$C$4:$N$4,0,0,)
M10M10=MATCHES(4,$C$4:$N$4,0,,0)
M11M11=MATCHES(4,$C$4:$N$4,0,0,0)
M12M12=MATCHES(4,$C$4:$N$4,0,1,)
M13M13=MATCHES(4,$C$4:$N$4,0,,1)
M14M14=MATCHES(4,$C$4:$N$4,0,1,1)
M15M15=MATCHES(4,$C$4:$N$4,0,2,)
M16M16=MATCHES(4,$C$4:$N$4,0,,2)
M17M17=MATCHES(4,$C$4:$N$4,0,2,2)
M18M18=MATCHES(4,$C$4:$N$4,0,3,)
M19M19=MATCHES(4,$C$4:$N$4,0,,3)
M20M20=MATCHES(4,$C$4:$N$4,0,3,3)
M21M21=MATCHES(4,$C$4:$N$4,0,7,)
M22M22=MATCHES(4,$C$4:$N$4,0,,7)
M23M23=MATCHES(4,$C$4:$N$4,0,7,7)
M24M24=MATCHES("a",$C$4:$N$4,0,0,)
M25M25=MATCHES("a",$C$4:$N$4,0,,0)
M26M26=MATCHES("a",$C$4:$N$4,0,0,0)
M27M27=MATCHES("a",$C$4:$N$4,0,1,)
M28M28=MATCHES("a",$C$4:$N$4,0,,1)
M29M29=MATCHES("a",$C$4:$N$4,0,1,1)
M30M30=MATCHES("a",$C$4:$N$4,0,2,)
M31M31=MATCHES("a",$C$4:$N$4,0,,2)
M32M32=MATCHES("a",$C$4:$N$4,0,2,2)
M33M33=MATCHES("a",$C$4:$N$4,0,3,)
M34M34=MATCHES("a",$C$4:$N$4,0,,3)
M35M35=MATCHES("a",$C$4:$N$4,0,3,3)
M36M36=MATCHES("a",$C$4:$N$4,0,7,)
M37M37=MATCHES("a",$C$4:$N$4,0,,7)
M38M38=MATCHES("a",$C$4:$N$4,0,7,7)
D40D40=MATCHES("",$C$4:$N$4,,0,0)
D41D41=MATCHES("",$C$4:$N$4,,1,1)
D42D42=MATCHES("",$C$4:$N$4,,2,2)
D43D43=MATCHES("",$C$4:$N$4,,,3)
D44D44=MATCHES("",$C$4:$N$4,,3,3)
D45D45=MATCHES("",$C$4:$N$4,,,7)
D46D46=MATCHES("",$C$4:$N$4,,7,7)
G40G40=MATCHES(,$C$4:$N$4,,0,0)
G41G41=MATCHES(,$C$4:$N$4,,1,1)
G42G42=MATCHES(,$C$4:$N$4,,2,2)
G43G43=MATCHES(,$C$4:$N$4,,,3)
G44G44=MATCHES(,$C$4:$N$4,,3,3)
G45G45=MATCHES(,$C$4:$N$4,,,7)
G46G46=MATCHES(,$C$4:$N$4,,7,7)
J40J40=MATCHES("",$C$4:$N$4,0,0,0)
J41J41=MATCHES("",$C$4:$N$4,0,1,1)
J42J42=MATCHES("",$C$4:$N$4,0,2,2)
J43J43=MATCHES("",$C$4:$N$4,0,,3)
J44J44=MATCHES("",$C$4:$N$4,0,3,3)
J45J45=MATCHES("",$C$4:$N$4,0,,7)
J46J46=MATCHES("",$C$4:$N$4,0,7,7)
M40M40=MATCHES(,$C$4:$N$4,0,0,0)
M41M41=MATCHES(,$C$4:$N$4,0,1,1)
M42M42=MATCHES(,$C$4:$N$4,0,2,2)
M43M43=MATCHES(,$C$4:$N$4,0,,3)
M44M44=MATCHES(,$C$4:$N$4,0,3,3)
M45M45=MATCHES(,$C$4:$N$4,0,,7)
M46M46=MATCHES(,$C$4:$N$4,0,7,7)



Please feel free to share any thoughts or suggestions.
 
Last edited:
Upvote 0
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
Excel Formula: 
=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.)
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top