Index Match Across Multiple String Criteria

[St Jimmy]

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.

	A	B	C
1	Vial	ID	Batch
2	V001	Z001	1
3	V002	Z002	1
4	V003	Z001	2
5	V004	Z003	1
6	V005	Z001	3
7	V006	Z003	2
8	V007	Z004	1

<tbody>
</tbody>

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!

 

[jim may]

Excel 2010

	A	B	C	D	E	F	G	H	I
1	A	B	C				ID	Z003
2	1	Vial	ID	Batch			Batch	2
3	2	V001	Z001	1			Vial	V006
4	3	V002	Z002	1
5	4	V003	Z001	2
6	5	V004	Z003	1
7	6	V005	Z001	3
8	7	V006	Z003	2
9	8	V007	Z004	1
10
11

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

Sheet1​

Array Formulas Cell Formula H3 {=INDEX(B3:B9,MATCH(H1&H2,C3:C9&D3:D9,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[ClaireS]

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.

=B2&C2
or 
=CONCATENATE(B2,C2)

Then you can use:

[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:

[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.

 

[AhoyNC]

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
	A	B	C	D	E	F
1	Vial	ID	Batch		ID	Z003
2	V001	Z001	1		Batch	2
3	V002	Z002	1
4	V003	Z001	2		Vial	V006
5	V004	Z003	1
6	V005	Z001	3
7	V006	Z003	2
8	V007	Z004	1
Sheet

 

[ClaireS]

@AhoyNC That is genius!

 

[St Jimmy]

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.

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!!!!!