Index Match multiple criteria ANY match

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
So, I understand how this formula works, not requiring an array (ctrl+shift+enter) but it is an exact match formula. All criteria must match.
How can I make it work if ANY of the criteria are matched:
(formula shows 3 criteria, can be 2 or more) -- Thanks

Code:
=INDEX(RESULT RNG,MATCH(1,INDEX((1[SUP]st[/SUP] criteria=match range)*(2[SUP]nd[/SUP] criteria=match range)*(3[SUP]rd[/SUP] criteria=match range),0,1),0))
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Have you tried replacing "*" with "+"?
 
Upvote 0
I was able to get @JoeMo 's idea to work:

Excel 2010
ABCDEF
1GERALDMazdaYellowMatch All:21
2Match 1:3
3Match 2:5
4#NamesCars:Colors
51BRADLEYMercuryBlack
62CRAIGKiaOrange
73ARNOLDSuzukiYellow
84MICHAELOldsmobileWhite
95FREDERICKMazdaYellow
106RONALDJeepOrange
117CLYDEFIATBlue
128SIDNEYMINIGreen
139MICHAELSaturnBlue
1410WILLIAMMcLarenRed
1511ERNESTLincolnBlue
1612JERRYFIATBlue
1713GORDONChryslerBlue
1814JAMESFordRed
1915STEVENPlymouthYellow
2016JAMESPontiac Silver
2117RONJaguarGreen
2218ROBERTKiaRed
2319THOMASDodgeWhite
2420ERIKChevroletYellow
2521GERALDMazdaYellow
2622MICHAELPontiacBlue
2723DERRICKMINI Silver
2824FELIXMitsubishiWhite
2925PAULAlfa Romeo Silver

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
F1=INDEX(A5:A29,MATCH(1,INDEX((B5:B29=B1)*(C5:C29=C1)*(D5:D29=D1),0,1),0))
F2=INDEX(A5:A29,MATCH(1,INDEX((B5:B29=B1)+(C5:C29=C1)+(D5:D29=D1),0,1),0))
F3=INDEX(A5:A29,MATCH(2,INDEX((B5:B29=B1)+(C5:C29=C1)+(D5:D29=D1),0,1),0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
I was able to get @JoeMo 's idea to work:

Excel 2010
ABCDEF
1GERALDMazdaYellowMatch All:21
2Match 1:3
3Match 2:5
4#NamesCars:Colors
51BRADLEYMercuryBlack
62CRAIGKiaOrange
73ARNOLDSuzukiYellow
84MICHAELOldsmobileWhite
95FREDERICKMazdaYellow
106RONALDJeepOrange
117CLYDEFIATBlue
128SIDNEYMINIGreen
139MICHAELSaturnBlue
1410WILLIAMMcLarenRed
1511ERNESTLincolnBlue
1612JERRYFIATBlue
1713GORDONChryslerBlue
1814JAMESFordRed
1915STEVENPlymouthYellow
2016JAMESPontiac Silver
2117RONJaguarGreen
2218ROBERTKiaRed
2319THOMASDodgeWhite
2420ERIKChevroletYellow
2521GERALDMazdaYellow
2622MICHAELPontiacBlue
2723DERRICKMINI Silver
2824FELIXMitsubishiWhite
2925PAULAlfa Romeo Silver

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
F1=INDEX(A5:A29,MATCH(1,INDEX((B5:B29=B1)*(C5:C29=C1)*(D5:D29=D1),0,1),0))
F2=INDEX(A5:A29,MATCH(1,INDEX((B5:B29=B1)+(C5:C29=C1)+(D5:D29=D1),0,1),0))
F3=INDEX(A5:A29,MATCH(2,INDEX((B5:B29=B1)+(C5:C29=C1)+(D5:D29=D1),0,1),0))

<tbody>
</tbody>

<tbody>
</tbody>

Maybe the issue is I am using full ranges since I can never know last row. This doesn't work, returns #N/A if I don't provide every criteria to match.
Code:
=INDEX(A:A,MATCH(1,INDEX((H2=B:B)+(I2=C:C)+(J2=D:D)+(K2=E:E)+(L2=F:F),0,1),0))
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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