How to choose high value from a name?

Question

Suppose a plan which contains the scores of a mini game sorted by players' name.

How to get the best score for each player?

BruceWayne · Accepted Answer · 2015-08-20T15:13:10.787

2

You can use a combination of Max and If:

In E1, place this formula (and enter by pressing CTRL+SHIFT+ENTER) and drag down: =MAX(IF($A$2:$A$11=$D2,$B$2:$B$11))

(Edit per @ExcelHero's comment, I had $D9).

edited Aug 20 '15 at 15:13

answered Aug 19 '15 at 20:17

BruceWayne

Matt Cremeens · Answer 2 · 2015-08-19T20:43:35.500

1

You could also sort the list by score in descending order and then just do a vlookup as in

=vlookup(D1, $A$1:$B$11, 2, false)

as vlookup will just return the first match.

edited Aug 19 '15 at 20:43

answered Aug 19 '15 at 20:28

Matt Cremeens

Thank you! I'll correct right now. So used to 0-based indices. – Matt Cremeens Aug 19 '15 at 20:34
Also, you would want to add false or 0 to the 4th parameter for an exact match. Right now its defaulting to an approximate match and will not always return the appropriate answer. – rwilson Aug 19 '15 at 20:41
I thought about that. I guess I felt it not likely to make a difference. I'll make the edit. Thanks. – Matt Cremeens Aug 19 '15 at 20:43

score 1 · Answer 3 · edited May 23 '17 at 11:44

1

Here's a link to a method that is completely formula driven.

It would use the array formula suggested by @BruceWayne, but also uses a formula that can create a distinct list of players.

edited May 23 '17 at 11:44

Community

answered Aug 19 '15 at 20:46

Stephen Lloyd

3 Answers3