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

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?

Find more posts tagged with

Accepted answers

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), ", ")

All comments

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! 👍️

America Ha

Hi Paul Newcome,

If people have the same highest score, how can I highlight the 2nd person?

I used this code but it's showing same name of the first person: =INDEX([Your Name]:[Your Name], MATCH([2nd Highest point]#, [TOTAL POINTS]:[TOTAL POINTS], 0))

Paul Newcome

@America Ha You would change your INDEX/MATCH to an INDEX/COLLECT which will allow you to specify to pull in the first/second/third/etc. in the event of a tie. However… This becomes MUCH more complex when trying to use the Sheet Summary fields. My suggestion would be to use the LARGE/DISTINCT and JOIN/DISTINCT to list ties out in a single string. Otherwise I would say to move this to a separate sheet where you can reference ranges of cells to make the formulas more manageable.

America Ha

Thanks, Paul. I have a problem: how can I stop Smartsheet from sending reminders for people to request access to the dashboard? as the smartsheet keep reminding them several times :(

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.