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!

 

[Eric W]

CA_Punit's formula should work fine for most cases, but there is an exception. If a value on an unselected row matches the max value, it could be displayed instead. For example:

Book1
	A	B	C	D	E	F	G	H
1	X	9	3	Unplanned		Criteria 1	3
2	A	9	3	Planned		Criteria 2	Planned
3	B	1	3	Planned
4	r	1	3	Planned
5	d	14	3	Unplanned			X	A
Sheet7

Cell Formulas
Range	Formula
G5	G5	=INDEX(A:A,MATCH(MAX(IF(C:C=3,IF(D:D="Planned",B:B))),B:B,0))
H5	H5	=INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$10)/($C$1:$C$10=G1)/($D$1:$D$10=G2)/($B$1:$B$10=AGGREGATE(14,6,($B$1:$B$10)/($C$1:$C$10=G1)/($D$1:$D$10=G2),1)),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Unfortunately, it's a bit of a pain to fix that particular issue, see the H5 formula. If that won't be a problem, stick with the shorter formula.

Also note that I used actual row numbers in the ranges. It's usually a bad idea to use whole column references in array formulas for performance reasons. Pick a row number that will be higher than the maximum row of your data.

 

[Innoguide]

Strange, but it is throwing #N/A error for me in Excel 365. Shouldn't need the {} for dynamic arrays since 365 handle them natively but I've tried it both ways and it's a no-gp

 

[CA_Punit]

Genious @Eric W

If it was feasible for me i would have applied for internship under you, Everytime i see your post i learn something new.
Anyway i going through all your post and hopefully within 1 -2 year i will complete studying your posts.

 

[Innoguide]

Sorry, Eric W - But your formula is delivering a #NUM error in Excel 365. Strange - it obviously works in your version. And now I see it is XL2BB not BBB

 

[Eric W]

Genious @Eric W

If it was feasible for me i would have applied for internship under you, Everytime i see your post i learn something new.
Anyway i going through all your post and hopefully within 1 -2 year i will complete studying your posts.

Good luck! That's how I got my start here. I thought I knew Excel pretty well, but I was stunned at what I didn't know. I kept looking at other posts and figuring out how everything worked, and now I'm passing on some of what I learned. Which is not to say I know it all now! I'm still learning new things!

 

[Eric W]

Sorry, Eric W - But your formula is delivering a #NUM error in Excel 365. Strange - it obviously works in your version. And now I see it is XL2BB not BBB

Your formula seems to be correct. The most obvious reason yours doesn't work is if the criteria in G1 and G2 don't match anything in column D. Since the 3's are on the right in both places, it seems they are defined as numbers. Does the "planned" in G2 match the planned in D3? It might have a hidden space or something. Enter =G2=D3 somewhere to see if Excel thinks they're a match.

 

[Innoguide]

Awesome Eric W - it was a trailing space in "Planned"

Thanks for all your help!

And you too CA_Punit!

 

[CA_Punit]

Cheers buddy

 

[Innoguide]

Playing further, any thoughts on this

Book1
	A	B	C	D	E	F	G
1						Criteria 1	3
2						Criteria 2	Planned
3	A	9.00	3	Unplanned
4	B	9.00	3	Planned		Result	B
5	r	1.00	3	Planned
6	d	14.00	3	Unplanned
Sheet1

Cell Formulas
Range	Formula
G4	G4	=INDEX(A:A,MATCH(1,(MAXIFS(B:B,C:C,G1, D:D,G2)=B:B)*(G1=C:C)*(G2=D:D),0))

 

[Eric W]

Playing further, any thoughts on this

Oh, absolutely! MAXIFS is a great approach. I had thought to use it, but I wasn't sure you had 365 at the time.