Formula Help - return maximum value for a person

[stuartw]

Good afternoon everyone.

I'm hoping for some help on a formula. I have a list of users where I want to return the maximum score in a given table for a given person. Example:

Name Score MaximumScore
Stu 4 7
Stu 7 7
Stu 3 7
Lee 5 9
Lee 9 9
Lee 8 9

The formula I want should reside in the "maximumscore" column (column C) and the desired results are above. As you can see I need the highest score by that person returning in the 3rd column. I got it working perfectly using the MAXIFS function, but this is not an option as it is not available in my clients version of excel (2010 i believe).

Can anyone help?

Thanks in advance
Stuart

 

[macattackpro]

Hi,

Formula for C2:

{=MAX(IF($A$1:$A$6=$A1,$B$1:$B$6))}

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[arthurbr]

A non array formula ( needs some twisting if ranges don't start on row1)
=INDEX($B$1:$B$6,SUMPRODUCT(MAX(($A$1:$A$6=$A1)*ROW($A$1:$A$6))))