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
528
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:
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
VBA Code: 
=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))
VBA Code: 
={-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}

IO (shortened form of ISOMITTED)
VBA Code: 
=LAMBDA(parameter,ISOMITTED(parameter))

JAI (module for "if_empty")
VBA Code: 
=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])
VBA Code: 
=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])
VBA Code: 
=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])
VBA Code: 
=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])
VBA Code: 
=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)
VBA Code: 
=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 "{}"])
VBA Code: 
=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)
VBA Code: 
=LAMBDA([parameter],IF(IO(parameter),0,parameter))

matches2.xlsx
ABCDEFGHIJKLMNOPQRST
1
2#CALC!basic types
3data type:numbernumberlogical (false)numbertext(real) blankerrorlogical (true)errortext(formula) blanknumber1dt:NUMBER
4cells:12FALSE-2&#DIV/0!TRUE#REF!b 62dt:TEXT
5position:1234567891011123dt:BLANK
6XTYPE($C$4:$N$4,2):114123164162314dt:LOGICAL
7XTYPE($C$4:$N$4,3):1142123116241164232116dt:ERROR
8
9advanced types
10match_typeexisting number lookupmatch_typenonexisting number lookup1dt:NUMBER
11none1none#CALC!2dt:TEXT
12010#CALC!31dt:BLANK_REAL
13121211232dt:BLANK_FORMULA
1421212212 41dt:LOGICAL_TRUE
15-14-112442dt:LOGICAL_FALSE
16-214-2124160dt:ERROR_EXTERNAL!
17(wrong argument) -4#N/A(wrong argument) 3#N/A161dt:ERROR_NULL!
18162dt:ERROR_DIV/0!
19match_typeexisting text lookupmatch_typenonexisting text lookup163dt:ERROR_VALUE!
20none10none#CALC!164dt:ERROR_REF!
210100#CALC!165dt:ERROR_NAME?
221#N/A1#N/A166dt:ERROR_NUM!
232#N/A2#N/A167dt:ERROR_N/A!
24-1#N/A-1#N/A168dt:ERROR_GETTING_DATA!
25-2#N/A-2#N/A169dt:ERROR_SPILL!
261610dt:ERROR_CONNECT!
27match_typeall blanks lookup (method 1)match_typeall blanks lookup (method 2)match_typeall blanks lookup (method 3)match_typeall blanks lookup (method 4)1611dt:ERROR_BLOCKED!
28none611none611none611none6111612dt:ERROR_UNKNOWN!
2906110611061106111613dt:ERROR_FIELD!
301#N/A1#N/A1#N/A1#N/A1614dt:ERROR_CALC!
312#N/A2#N/A2#N/A2#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
34match_typeSpecific blanks lookup (method 1)match_typeSpecific blanks lookup (method 2)
35none6none6
360606
371#N/A1#N/A
382#N/A2#N/A
39-1#N/A-1#N/A
40-2#N/A-2#N/A
41none11none11
42011011
431#N/A1#N/A
442#N/A2#N/A
45-1#N/A-1#N/A
46-2#N/A-2#N/A
47
48match_typeall errors lookup (method 1)match_typeall errors lookup (method 2)
49none79none79
50079079
511#N/A1#N/A
522#N/A2#N/A
53-1#N/A-1#N/A
54-2#N/A-2#N/A
55match_typespecific errors lookup (method 1)match_typespecific errors lookup (method 2)match_typespecific errors lookup (method 3)match_typespecific errors lookup (method 4)
56none#CALC!0#CALC!none#CALC!0#CALC!
57none#CALC!0#CALC!none#CALC!0#CALC!
58none707none707
59none#CALC!0#CALC!none#CALC!0#CALC!
60none909none909
61none#CALC!0#CALC!none#CALC!0#CALC!
62none#CALC!0#CALC!none#CALC!0#CALC!
63none#CALC!0#CALC!none#CALC!0#CALC!
64none#CALC!0#CALC!none#CALC!0#CALC!
65none#CALC!0#CALC!none#CALC!0#CALC!
66none#CALC!0#CALC!none#CALC!0#CALC!
67none#CALC!0#CALC!none#CALC!0#CALC!
68none#CALC!0#CALC!none#CALC!0#CALC!
69none#CALC!0#CALC!none#CALC!0#CALC!
70none#CALC!0#CALC!none#CALC!0#CALC!
71if_empty 1234512345if_empty "book"bookif_empty "" (blank) if_empty #DIV/0! (error)#DIV/0!
72if_empty TRUETRUEif_empty FALSEFALSEif_empty {1,2} array#N/Aif_empty {1,"test"} array#N/A
73
74match_typeTRUE logicals lookup (method 1)match_typeTRUE logicals lookup (method 2)match_typeTRUE logicals lookup (method 3)match_typeall logicals lookup (method 1)
75none8none8none8none38
76080808038
771#N/A1#N/A1#N/A1#N/A
782#N/A2#N/A2#N/A2#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
81match_typeFALSE logicals lookup (method 1)match_typeFALSE logicals lookup (method 2)match_typeFALSE logicals lookup (method 3)match_typeall logicals lookup (method 2)
82none3none3none3none38
83030303038
841#N/A1#N/A1#N/A1#N/A
852#N/A2#N/A2#N/A2#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
89output_orientationoutput_orientation
90omitted1212omitted021
91012121111212
9211212222
9321121212
942
9512
96
correct_params_horizontal
Cell Formulas
RangeFormula
B2B2=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)))
I4I4=1/0
K4K4=LOOKUP(#REF!,#REF!)
M4M4=IF(M1>2,1,"")
C6:N6C6=XTYPE($C$4:$N$4,2)
C7:N7C7=XTYPE($C$4:$N$4,3)
P14P14=LET(x,"",TEXT(x,10))
C11C11=MATCHES(1,$C$4:$N$4)
C12C12=MATCHES(1,$C$4:$N$4,0)
C13:D13C13=MATCHES(1,$C$4:$N$4,1)
C14:E14C14=MATCHES(1,$C$4:$N$4,2)
C15C15=MATCHES(1,$C$4:$N$4,-1)
C16:D16C16=MATCHES(1,$C$4:$N$4,-2)
C17C17=MATCHES(1,$C$4:$N$4,-4)
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)
J17J17=MATCHES(5,$C$4:$N$4,3)
C20C20=MATCHES("b",$C$4:$N$4)
C21C21=MATCHES("b",$C$4:$N$4,0)
C22C22=MATCHES("b",$C$4:$N$4,1)
C23C23=MATCHES("b",$C$4:$N$4,2)
C24C24=MATCHES("b",$C$4:$N$4,-1)
C25C25=MATCHES("b",$C$4:$N$4,-2)
J20J20=MATCHES("app",$C$4:$N$4)
J21J21=MATCHES("app",$C$4:$N$4,0)
J22J22=MATCHES("app",$C$4:$N$4,1)
J23J23=MATCHES("app",$C$4:$N$4,2)
J24J24=MATCHES("app",$C$4:$N$4,-1)
J25J25=MATCHES("app",$C$4:$N$4,-2)
C28:D28C28=MATCHES(,$C$4:$N$4,)
C29:D29C29=MATCHES(,$C$4:$N$4,0)
C30C30=MATCHES(,$C$4:$N$4,1)
C31C31=MATCHES(,$C$4:$N$4,2)
C32C32=MATCHES(,$C$4:$N$4,-1)
C33C33=MATCHES(,$C$4:$N$4,-2)
G28:H28G28=MATCHES(,$C$4:$N$4,,,,0)
G29:H29G29=MATCHES(,$C$4:$N$4,0,,,0)
G30G30=MATCHES(,$C$4:$N$4,1,,,0)
G31G31=MATCHES(,$C$4:$N$4,2,,,0)
G32G32=MATCHES(,$C$4:$N$4,-1,,,0)
G33G33=MATCHES(,$C$4:$N$4,-2,,,0)
K28:L28K28=MATCHES("",$C$4:$N$4,)
K29:L29K29=MATCHES("",$C$4:$N$4,0)
K30K30=MATCHES("",$C$4:$N$4,1)
K31K31=MATCHES("",$C$4:$N$4,2)
K32K32=MATCHES("",$C$4:$N$4,-1)
K33K33=MATCHES("",$C$4:$N$4,-2)
O28:P28O28=MATCHES("",$C$4:$N$4,,,,0)
O29:P29O29=MATCHES("",$C$4:$N$4,0,,,0)
O30O30=MATCHES("",$C$4:$N$4,1,,,0)
O31O31=MATCHES("",$C$4:$N$4,2,,,0)
O32O32=MATCHES("",$C$4:$N$4,-1,,,0)
O33O33=MATCHES("",$C$4:$N$4,-2,,,0)
G35G35=MATCHES(,$C$4:$N$4,,,,1)
G36G36=MATCHES(,$C$4:$N$4,0,,,1)
G37G37=MATCHES(,$C$4:$N$4,1,,,1)
G38G38=MATCHES(,$C$4:$N$4,2,,,1)
G39G39=MATCHES(,$C$4:$N$4,-1,,,1)
G40G40=MATCHES(,$C$4:$N$4,-2,,,1)
G41G41=MATCHES(,$C$4:$N$4,,,,2)
G42G42=MATCHES(,$C$4:$N$4,0,,,2)
G43G43=MATCHES(,$C$4:$N$4,1,,,2)
G44G44=MATCHES(,$C$4:$N$4,2,,,2)
G45G45=MATCHES(,$C$4:$N$4,-1,,,2)
G46G46=MATCHES(,$C$4:$N$4,-2,,,2)
O35O35=MATCHES("",$C$4:$N$4,,,,1)
O36O36=MATCHES("",$C$4:$N$4,0,,,1)
O37O37=MATCHES("",$C$4:$N$4,1,,,1)
O38O38=MATCHES("",$C$4:$N$4,2,,,1)
O39O39=MATCHES("",$C$4:$N$4,-1,,,1)
O40O40=MATCHES("",$C$4:$N$4,-2,,,1)
O41O41=MATCHES("",$C$4:$N$4,,,,2)
O42O42=MATCHES("",$C$4:$N$4,0,,,2)
O43O43=MATCHES("",$C$4:$N$4,1,,,2)
O44O44=MATCHES("",$C$4:$N$4,2,,,2)
O45O45=MATCHES("",$C$4:$N$4,-1,,,2)
O46O46=MATCHES("",$C$4:$N$4,-2,,,2)
C49:D49C49=MATCHES(,$C$4:$N$4,,,-1)
C50:D50C50=MATCHES(,$C$4:$N$4,0,,-1)
C51C51=MATCHES(,$C$4:$N$4,1,,-1)
C52C52=MATCHES(,$C$4:$N$4,2,,-1)
C53C53=MATCHES(,$C$4:$N$4,-1,,-1)
C54C54=MATCHES(,$C$4:$N$4,-2,,-1)
J49:K49J49=MATCHES("",$C$4:$N$4,,,-1)
J50:K50J50=MATCHES("",$C$4:$N$4,0,,-1)
J51J51=MATCHES("",$C$4:$N$4,1,,-1)
J52J52=MATCHES("",$C$4:$N$4,2,,-1)
J53J53=MATCHES("",$C$4:$N$4,-1,,-1)
J54J54=MATCHES("",$C$4:$N$4,-2,,-1)
C56C56=MATCHES(,$C$4:$N$4,,,0)
C57C57=MATCHES(,$C$4:$N$4,,,1)
C58C58=MATCHES(,$C$4:$N$4,,,2)
C59C59=MATCHES(,$C$4:$N$4,,,3)
C60C60=MATCHES(,$C$4:$N$4,,,4)
C61C61=MATCHES(,$C$4:$N$4,,,5)
C62C62=MATCHES(,$C$4:$N$4,,,6)
C63C63=MATCHES(,$C$4:$N$4,,,7)
C64C64=MATCHES(,$C$4:$N$4,,,8)
C65C65=MATCHES(,$C$4:$N$4,,,9)
C66C66=MATCHES(,$C$4:$N$4,,,10)
C67C67=MATCHES(,$C$4:$N$4,,,11)
C68C68=MATCHES(,$C$4:$N$4,,,12)
C69C69=MATCHES(,$C$4:$N$4,,,13)
C70C70=MATCHES(,$C$4:$N$4,,,14)
C71C71=MATCHES(,$C$4:$N$4,,,14,,,12345)
C72C72=MATCHES(,$C$4:$N$4,,,14,,,TRUE)
G56:G57G56=MATCHES(,$C$4:$N$4,0,,1)
G58G58=MATCHES(,$C$4:$N$4,0,,2)
G59G59=MATCHES(,$C$4:$N$4,0,,3)
G60G60=MATCHES(,$C$4:$N$4,0,,4)
G61G61=MATCHES(,$C$4:$N$4,0,,5)
G62G62=MATCHES(,$C$4:$N$4,0,,6)
G63G63=MATCHES(,$C$4:$N$4,0,,7)
G64G64=MATCHES(,$C$4:$N$4,0,,8)
G65G65=MATCHES(,$C$4:$N$4,0,,9)
G66G66=MATCHES(,$C$4:$N$4,0,,10)
G67G67=MATCHES(,$C$4:$N$4,0,,11)
G68G68=MATCHES(,$C$4:$N$4,0,,12)
G69G69=MATCHES(,$C$4:$N$4,0,,13)
G70G70=MATCHES(,$C$4:$N$4,0,,14)
G71G71=MATCHES(,$C$4:$N$4,0,,14,,,"book")
G72G72=MATCHES(,$C$4:$N$4,,,14,,,FALSE)
K56:K57K56=MATCHES("",$C$4:$N$4,,,1)
K58K58=MATCHES("",$C$4:$N$4,,,2)
K59K59=MATCHES("",$C$4:$N$4,,,3)
K60K60=MATCHES("",$C$4:$N$4,,,4)
K61K61=MATCHES("",$C$4:$N$4,,,5)
K62K62=MATCHES("",$C$4:$N$4,,,6)
K63K63=MATCHES("",$C$4:$N$4,,,7)
K64K64=MATCHES("",$C$4:$N$4,,,8)
K65K65=MATCHES("",$C$4:$N$4,,,9)
K66K66=MATCHES("",$C$4:$N$4,,,10)
K67K67=MATCHES("",$C$4:$N$4,,,11)
K68K68=MATCHES("",$C$4:$N$4,,,12)
K69K69=MATCHES("",$C$4:$N$4,,,13)
K70K70=MATCHES("",$C$4:$N$4,,,14)
K71K71=MATCHES("",$C$4:$N$4,,,14,,,"")
K72K72=MATCHES(,$C$4:$N$4,,,14,,,{1,2})
O56:O57O56=MATCHES("",$C$4:$N$4,0,,1)
O58O58=MATCHES("",$C$4:$N$4,0,,2)
O59O59=MATCHES("",$C$4:$N$4,0,,3)
O60O60=MATCHES("",$C$4:$N$4,0,,4)
O61O61=MATCHES("",$C$4:$N$4,0,,5)
O62O62=MATCHES("",$C$4:$N$4,0,,6)
O63O63=MATCHES("",$C$4:$N$4,0,,7)
O64O64=MATCHES("",$C$4:$N$4,0,,8)
O65O65=MATCHES("",$C$4:$N$4,0,,9)
O66O66=MATCHES("",$C$4:$N$4,0,,10)
O67O67=MATCHES("",$C$4:$N$4,0,,11)
O68O68=MATCHES("",$C$4:$N$4,0,,12)
O69O69=MATCHES("",$C$4:$N$4,0,,13)
O70O70=MATCHES("",$C$4:$N$4,0,,14)
O71O71=MATCHES("",$C$4:$N$4,0,,14,,,#DIV/0!)
O72O72=MATCHES(,$C$4:$N$4,,,14,,,{1,"test"})
C75C75=MATCHES(TRUE,$C$4:$N$4)
C76C76=MATCHES(TRUE,$C$4:$N$4,0)
C77C77=MATCHES(TRUE,$C$4:$N$4,1)
C78C78=MATCHES(TRUE,$C$4:$N$4,2)
C79C79=MATCHES(TRUE,$C$4:$N$4,-1)
C80C80=MATCHES(TRUE,$C$4:$N$4,-2)
G75G75=MATCHES(,$C$4:$N$4,,,,,1)
G76G76=MATCHES(,$C$4:$N$4,0,,,,1)
G77G77=MATCHES(,$C$4:$N$4,1,,,,1)
G78G78=MATCHES(,$C$4:$N$4,2,,,,1)
G79G79=MATCHES(,$C$4:$N$4,-1,,,,1)
G80G80=MATCHES(,$C$4:$N$4,-2,,,,1)
K75K75=MATCHES("",$C$4:$N$4,,,,,1)
K76K76=MATCHES("",$C$4:$N$4,0,,,,1)
K77K77=MATCHES("",$C$4:$N$4,1,,,,1)
K78K78=MATCHES("",$C$4:$N$4,2,,,,1)
K79K79=MATCHES("",$C$4:$N$4,-1,,,,1)
K80K80=MATCHES("",$C$4:$N$4,-2,,,,1)
O75:P75O75=MATCHES(,$C$4:$N$4,,,,,-1)
O76:P76O76=MATCHES(,$C$4:$N$4,0,,,,-1)
O77O77=MATCHES(,$C$4:$N$4,1,,,,-1)
O78O78=MATCHES(,$C$4:$N$4,2,,,,-1)
O79O79=MATCHES(,$C$4:$N$4,-1,,,,-1)
O80O80=MATCHES(,$C$4:$N$4,-2,,,,-1)
C82C82=MATCHES(FALSE,$C$4:$N$4)
C83C83=MATCHES(FALSE,$C$4:$N$4,0)
C84C84=MATCHES(FALSE,$C$4:$N$4,1)
C85C85=MATCHES(FALSE,$C$4:$N$4,2)
C86C86=MATCHES(FALSE,$C$4:$N$4,-1)
C87C87=MATCHES(FALSE,$C$4:$N$4,-2)
G82G82=MATCHES(,$C$4:$N$4,,,,,0)
G83G83=MATCHES(,$C$4:$N$4,0,,,,0)
G84G84=MATCHES(,$C$4:$N$4,1,,,,0)
G85G85=MATCHES(,$C$4:$N$4,2,,,,0)
G86G86=MATCHES(,$C$4:$N$4,-1,,,,0)
G87G87=MATCHES(,$C$4:$N$4,-2,,,,0)
K82K82=MATCHES("",$C$4:$N$4,,,,,0)
K83K83=MATCHES("",$C$4:$N$4,0,,,,0)
K84K84=MATCHES("",$C$4:$N$4,1,,,,0)
K85K85=MATCHES("",$C$4:$N$4,2,,,,0)
K86K86=MATCHES("",$C$4:$N$4,-1,,,,0)
K87K87=MATCHES("",$C$4:$N$4,-2,,,,0)
O82:P82O82=MATCHES("",$C$4:$N$4,,,,,-1)
O83:P83O83=MATCHES("",$C$4:$N$4,0,,,,-1)
O84O84=MATCHES("",$C$4:$N$4,1,,,,-1)
O85O85=MATCHES("",$C$4:$N$4,2,,,,-1)
O86O86=MATCHES("",$C$4:$N$4,-1,,,,-1)
O87O87=MATCHES("",$C$4:$N$4,-2,,,,-1)
H91:H93H91=MATCHES(1,TRANSPOSE($C$4:$N$4),2,)
I91:I93I91=MATCHES(1,TRANSPOSE($C$4:$N$4),2,0)
J91:J93J91=MATCHES(1,TRANSPOSE($C$4:$N$4),2,2)
K91:M91K91=MATCHES(1,TRANSPOSE($C$4:$N$4),2,1)
C90:E90C90=MATCHES(1,$C$4:$N$4,2,)
C91:E91C91=MATCHES(1,$C$4:$N$4,2,0)
C92:E92C92=MATCHES(1,$C$4:$N$4,2,1)
C93:C95C93=MATCHES(1,$C$4:$N$4,2,2)
Dynamic array formulas.
 
cannot use

1677806677866.png
 
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.
 
i put new lambda for matches it cant save
 
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.
 
It's missing a comma.
Excel Formula: 
42,IF(j=42,1,0)))
            NA())),
 
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.
 
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.
 
I see, that's an excellent idea! Will do that for all my future posts/updates.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

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