Index Match with Max

[carlidee]

Hello

I'm looking to using a combo of index/match and max to find the highest value associated with a name

This is what I have for cell E3, but it seems to be pulling the overall max number:
=INDEX(MAX($B$3:$B$8),MATCH(D3:D7,$A$3:$A$8,0))

Any ideas?

 

[maabadi]

Try these with Press CTRL+SHIFT+ENTER

=MAX(($A$3:$A$8=D3)*$B$3:$B$8)

OR

=MAX(IF($A$3:$A$8=D3,$B$3:$B$8))

 

[jasonb75]

Try

=AGGREGATE(14,6,$B$3:$B$8/($A$3:$A$8=D3),1)

Or if you have a version of excel newer than 2016

=MAXIFS($B$3:$B$8,$A$3:$A$8,D3)

 

[carlidee]

Try these with Press CTRL+SHIFT+ENTER

=MAX(($A$3:$A$8=D3)*$B$3:$B$8)

OR

=MAX(IF($A$3:$A$8=D3,$B$3:$B$8))[/COD
[/QUOTE]

Hi - thanks for jumping in to help

So what I'm asking about is actually intended to be an extension of an IF formula, that is instructing it what to do if the first condition is false. Therefore, I dont believe an array would work here.

Do you have any other recommendations?

Thank you
Carli

 

[AhoyNC]

You could also use MAXIFS function.

=MAXIFS($B$3:$B$8,$A$3:$A$8,D3)

 

[carlidee]

Try

=AGGREGATE(14,6,$B$3:$B$8/($A$3:$A$8=D3),1)

Or if you have a version of excel newer than 2016

=MAXIFS($B$3:$B$8,$A$3:$A$8,D3)

MAXIFS worked perfect thank you!!

 

[Fluff]

If you have the MAXIFS then you are using either 365 or 2019. I would suggest that you update your account details to show what you are using.