Find index for item with maximum value and multiple criteria

[Innoguide]

I need to find the item in column A with the highest value in column B where the value in column C and column D also match certain criteria

Starting simply without the criteria I would use:

=index(A:A,match(max(B:B),B:B,0)

And to find a match for multiple criteria I would use:

=index(A:A,match(1,Criteria1=C:C)*(Criteria2=D:D),0))

Any suggestions for how I could combine them to find the value in column A that has the maximum result in column B and matches the criteria for the other 2 columns

Thanks in advance!

 

[CA_Punit]

=INDEX(A:A,MATCH(MAX(if(C:C=criteria1,if(D:D=criteria2,B:B)),B:B,0))

Try this with Ctrl+shift+Enter

 

[Innoguide]

=INDEX(A:A,MATCH(MAX(if(C:C=criteria1,if(D:D=criteria2,B:B)),B:B,0))

Try this with Ctrl+shift+Enter

Hmmm... coming up as too few arguments entered

 

[CA_Punit]

I missed out one Bracket

=INDEX(A:A,MATCH(MAX(if(C:C=criteria1,if(D:D=criteria2,B:B))),B:B,0))

 

[Innoguide]

What seems to work is
=INDEX(A:A,MATCH(MAX(if(C:C=criteria1,B:B),if(D:D=criteria2,B:B)),B:B,0))
with Ctrl+shift+enter

 

[Innoguide]

Thanks for pointing me in the right direction!

 

[Innoguide]

What seems to work is
=INDEX(A:A,MATCH(MAX(if(C:C=criteria1,B:B),if(D:D=criteria2,B:B)),B:B,0))
with Ctrl+shift+enter

OK - mine isn't working either


and =INDEX(A:A,MATCH(MAX(if(C:C=criteria1,if(D:D=criteria2,B:B))),B:B,0)) comes up an error too

 

[CA_Punit]

You have to edit the criteria with your own and after it is done press control shift enter

if not resolved please care to post a sample data using xl2bbb addin

 

[Innoguide]

Yes, I had done both, but here's what I get with formula in cell G4. I'm having problems finding any XL2bbb addin so my apologies but here's a simple screenshot

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I
1	A	9	3	Planned
2	B	1	3	Planned
3	r	1	3	Planned
4	d	14	3	Unplanned			A
5
6
7
Sheet3

Cell Formulas
Range	Formula
G4	G4	=INDEX(A:A,MATCH(MAX(IF(C:C=3,IF(D:D="Planned",B:B))),B:B,0))