Index Match Across Multiple String Criteria

S

St Jimmy

New Member
Joined
Oct 29, 2015
Messages
37
I'm sure there is an easy solution, but my brain (nor google it seems) can find it...

I'm trying to match two string criteria in an index match formula and I dont want to use an array.
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Vial[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Batch[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]V001[/TD]
[TD="align: center"]Z001[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]V002[/TD]
[TD="align: center"]Z002[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]V003[/TD]
[TD="align: center"]Z001[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]V004[/TD]
[TD="align: center"]Z003[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]V005[/TD]
[TD="align: center"]Z001[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]V006[/TD]
[TD="align: center"]Z003[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]V007[/TD]
[TD="align: center"]Z004[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

I'd like to enter the ID and the Batch and get the Vial returned.
I'd like something like this: Index(A2:A8,Match(and("Z003",2),and(B2:B8,C2:C8),0))
And have V006 returned.

I'd prefer no arrays, is this possible? Any help is greatly appreciated!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Excel 2010
ABCDEFGHI

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Z003[/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Vial[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Batch[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Batch[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]V001[/TD]
[TD="align: center"]Z001[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Vial[/TD]
[TD="align: center"]V006[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]V002[/TD]
[TD="align: center"]Z002[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]V003[/TD]
[TD="align: center"]Z001[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]V004[/TD]
[TD="align: center"]Z003[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]V005[/TD]
[TD="align: center"]Z001[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]V006[/TD]
[TD="align: center"]Z003[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]V007[/TD]
[TD="align: center"]Z004[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]{=INDEX(B3:B9,MATCH(H1&H2,C3:C9&D3:D9,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You could do it with a helper column. In Column D, CONCATENATE or use & to join the two entries to give Z0011, Z0021, Z0012 etc.
Code: 
=B2&C2
or 
=CONCATENATE(B2,C2)
Then you can use:
Code: 
[FONT=Verdana][FONT=Verdana]=INDEX(A2:A8,MATCH(G1&G2,D2:D8,0))[/FONT][/FONT]
(my lookup ID and batch are in G1 and G2).

I think that without the helper column, you will need an array formula to concatenate the values in the two arrays:
Code: 
[FONT=Verdana]=INDEX(A2:A8,MATCH(G1&G2,B2:B8&C2:C8))[/FONT]
then enter with Ctrl-Shift-Enter. Excel will add curly braces around the entire formula.
 
Upvote 0
If you have Excel ver. 2010 or later you could use this formula. This is still an array formula, but you don't have to use CTRL-SHIFT-ENTER. Just ENTER.
Excel Workbook
ABCDEF
1VialIDBatchIDZ003
2V001Z0011Batch2
3V002Z0021
4V003Z0012VialV006
5V004Z0031
6V005Z0013
7V006Z0032
8V007Z0041
Sheet
 
Upvote 0
Thank you AhoyNC, and go Canes!

I'm going to bump the stakes on this post (hijacking my own thread in the process).
What I'm trying to do is use this code in a VBA macro in place of my quicksort function. I've found that the application.worksheetfunction index(match works about 5x as fast as my quicksort function (ouch, my pride) and I'm trying to replace all my calls to my quicksort with the application.worksheetfunction type (and time everything to make sure it is really saving me time).

That said, I tried to convert your formula to VBA code and it keeps throwing a type-mismatch error.

BatchBioInfo is a variant 2D array, ID is a string, i is an int, were in a nested for loop j in i.

Code: 
BatchBioInfo (i,j) = Application.WorksheetFunction.Index(Sheets("VIALDB").Range("$A$2:$A$8"), Application.WorksheetFunction.Aggregate(15,6, (Sheets("VIALDB").Range("$A$2:$A$8").Row - Sheets("VIALDB").Range("$A$2").Row + 1) / ((Sheets("VIALDB").Range("$B$2:$B$8").Value2 = ID) * (Sheets("VIALDB").Range("$C$2:$C$8").Value2 = i)), 1))

Any thoughts why this code wont work in VBA?
Thank you!!!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Match with criteria and starting index
Replies
4
Views
82
mrblister
M
M
  • Question Question
A search formula (like index match) that can pull the most recent date when multiple matches may occur based on the criteria figure.
Replies
2
Views
152
MARZIOTULLIO
M
M
  • Solved
Index and Match
Replies
3
Views
133
Peter_SSs
Peter_SSs
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
318
acool
A
D
  • Solved
Index/match multiple criteria not working - HELP please
Replies
5
Views
335
NickFru
N

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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