Formula to get name of top scorers

Paul Pindell

I have the following Data Set:

Name Score

Bob 0

Sally 0

Mary 3

James 12

Matt 0

Pat 32

Sanjay 1

Vincent 22

Scott 12

Srikanth 0

Deb 16

Wynton 0

I am using a formula like this:

=LARGE([Score]$1:[Score]$12 1)

to find the value of the highest score

=LARGE([Score]$1:[Score]$12 2)

to find the second highest

=LARGE([Score]$1:[Score]$12, 3)

to find the third highest. populate three cells like below

32

22

16

That all works great. What I'd like to do now is populate the column prior to the 32, 22, and 16 values with the name on the row where that value was found.

so for 32 the name is Pat, for 22 it is Vincent, for 16 it is Deb.

so the resulting two columns and 3 rows would look like this.

Pat. 32

Vincent 22

Deb. 16

I've tried variations on this formula below with now success.

=LOOKUP("LARGE([Score]1:[Score]12, 1)", [Name]1:[Score]12, 1)

Any Suggestions?

Paul Newcome

I am going to suggest tweaking your LARGE formula to account for multiple people having that same score.

=LARGE(DISTINCT([Score]$1:[Score]$12), #)

Then to capture the names in another column it would look something along the lines of

=JOIN(COLLECT(Name$1:Name$12, Score$1:Score$12, [Large Column Name]@row), ", ")

Paul Newcome

I am going to suggest tweaking your LARGE formula to account for multiple people having that same score.

=LARGE(DISTINCT([Score]$1:[Score]$12), #)

Then to capture the names in another column it would look something along the lines of

=JOIN(COLLECT(Name$1:Name$12, Score$1:Score$12, [Large Column Name]@row), ", ")

Paul Pindell

@Paul Newcome,

Thank you for this. the Join(Collect...) function worked great.

Paul Newcome

Happy to help! 👍️