Max Value IF

[AK_Excel_13]

I have a list of 200 names. Each name is shown ten times. This makes my spreadsheet have 2,001 rows of data.
In the columns I have Name, Score, and Max Score. Max Score is empty.
If Joe has scores of 7,4,6,3,8,5,3,2,7, and 4 then the Max Score for him should be 8 (as that is his highest score).
If Steve has scores of 6,3,7,6,5,9,2,4,3, and 3 then the Max Score for him should be 9 (as that is his highest score).

How do I do this for all 200 names?

 

[a massive zebra]

Try this, entered into cell C2 and copied down:

=MAX(IF($A$2:$A$2001=A2,$B$2:$B$2001))

Enter with CTRL+SHTF+ENTR, not just enter.

 

[gaz_chops]

Try this entered with Ctrl, Shift & Enter

=MAX(IF($A$2:$A$2001=A2,$B$2:$B$2001))

 

[a massive zebra]

This also seems to work:

=SUMPRODUCT(MAX((A$2:A$2001=A2)*B$2:B$2001))

It is arguably a superior formula as it can be entered normally and probably calculates slightly faster.

 

[AK_Excel_13]

This one worked.
=SUMPRODUCT(MAX((A$2:A$2001=A2)*B$2:B$2001))

Thanks everyone!

 

[gaz_chops]

You're welcome, both should work.