0

Example: https://docs.google.com/spreadsheets/d/1vE5DSC5bQMuHGMk3IRtLqFpTlr6AsfFvk0hMYZLts7o/edit?usp=sharing

Let's say I have the following table

Table 1 is titled "Scoring"

Tom Mike Carl
G1 4 1 5
G2 5 2 7
Total 9 3 12

I know I can use functions to determine what number is the largest (First place), second largest (Second Place) and third-largest (third place) number is...

=LARGE(B4:D4, 1)

=LARGE(B4:D4, 2)

=LARGE(B4:D4, 3)

And that will return 12 9 and 3 respectively. Is it possible to take those numbers, and get the name (from row 1) that corresponds to them? So my output would be Carl Tom and Mike in that order. If needed, two steps can be made (So step one is to get the number, and step two (In a different cell) is to find the name.

Here is what I want the final output to look like:

Tom Mike Carl
G1 4 1 5 1st Carl
G2 5 2 7 2nd Mike
Total 9 3 12 3rd Tom

I guess I should mention that the names are stated externally, and are shown in the table above using =Info!B[number] and the totals are calculated from adding the G1 and G2 scores together.

Improve this question
4
  • 1
    Either you've made an error in your post or I am not understanding you clearly. You said that the order of values should be 12, 9, 3 ... but then you said the corresponding names should be "Carl Mike and Tom in that order." But as I see it, 12, 9, 3 corresponds to the names in order of Carl Tom Mike instead. Also, it is unclear where you would like the output. It would help move things along if you shared a link to a sample spreadsheet containing the posted data and clearly indicating where the results should go, as it will save the volunteers making their own spreadsheet first.
    – Erik Tyler
    Jan 11 at 7:30
  • Thanks for pointing that out. I fixed that error and added a demo spreadsheet
    – Greenreader9
    Jan 11 at 17:49
  • You currently have your sheet link permission set to "Comment only" and "Cannot copy." So there is no way to work with the data, even by making a copy of the spreadsheet and working in that. Please change the permission to at least remove the copy restriction.
    – Erik Tyler
    Jan 11 at 18:58
  • Sorry about that, I fixed it.
    – Greenreader9
    Jan 11 at 19:33
1

Try this formula in cell G2, and copy down for as many places as you calculate.

=index(B$1:D$1,1,match(F2,$B$5:$D$5,0))

Sample sample1

Logic

  • In this sample, the values in Column E and Column F have been populated. Column E = text, Column F uses the LARGE function.
  • match - returns the column number in the range $B$5:$D$5 that contains the value in column F
  • index - returns the value (Name) in the range B$1:D$1 that is in the column number returned by match

ADDENDUM - Options#2

In this example, only the text in Column E ("1st", "2nd", "3rd") is provided, the values in Column F and G are determined dynamically.

Winners Scores
Enter this formula in cell F2 and copy down:
=large($A$5:$D$5,value(left(E2,1)))

Winner
You could use the existing formula as described above, or use =index($B$1:$D$1,1,match(large($A$5:$D$5,value(left(E2,1))),$B$5:$D$5,0))
for a completely dynamic formula.

option2

Improve this answer
1
  • As a matter of interest: you have a header in row#2 for the round number. But each player has two scores - one for group 1, another for group 12. Is the "Round" header actually meaningful - what unique information does it identify?
    – Tedinoz
    Jan 13 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.