0

I have a column of un-ordered numbers along with a column of names. In a different sheet I calculate the Max of the numbers column with the formula:

 =MAX('Form Responses 1'!E:E)

Now I would like to find what name belongs to that max value.

3
  • I don't think you would want me to fill the page with a bunch of not-working-solutions, anonymous.
    – rearThing
    Sep 22 '17 at 18:35
  • Yes, I would like that.
    – TheMaster
    Sep 22 '17 at 19:07
  • Sorry, there is already a lot of trash out there and apparently some angst too.
    – rearThing
    Sep 22 '17 at 20:31
2

MATCH the determined max value in the column of numbers and apply the result from that in an INDEX function applied to the column of names. Issues may arise if the max value is not unique.

1
  • 1
    Cant vote up but thank you.
    – rearThing
    Sep 23 '17 at 14:59
2

Use QUERY.

Example

=QUERY('Form Responses 1'!A2:E,"SELECT B,E ORDER BY E DESC LIMIT 1")

The above formula assumes that the first row include column labels and that we don't want them on the results. Also it assumes that names are on column B and that it's required to return both values, name and the max value. If you only want the name use

=QUERY('Form Responses 1'!A2:E,"SELECT B ORDER BY E DESC LIMIT 1")
0
2

@pnuts's answer is good, but not the only way. Another way to arrive at the solution, assuming your names are in A:A

=FILTER(A:A, 'Form Responses 1'!E:E = MAX('Form Responses 1'!E:E))
5
  • 1
    If you found pnuts' answer as a good answer why it doesn't has any vote? :)
    – Rubén
    Sep 22 '17 at 15:07
  • 2
    @pnuts Maybe someone found helpful to know that your answer is helpful :)
    – Rubén
    Sep 22 '17 at 15:14
  • 1
    @pnuts: There was a typo (a missing closing parenthesis). Besides that, the formula will work if it is added to the Form Responses 1 sheet.
    – Rubén
    Sep 22 '17 at 15:34
  • Thanks, Ruben, that makes a lot of sense. Cant vote up yet...
    – rearThing
    Sep 23 '17 at 14:32
  • 1
    Rubén: Right, spot on, upvoting @pnuts :-)
    – ttarchala
    Sep 25 '17 at 21:50
2

@ttarchala's answer is good, but not the only way. Assuming names are in B:B, The shortest one:

=INDEX(SORT('Form Responses 1'!A2:E,5,0),1,2)

SORTs the form responses in descending order based on the 5th column. INDEX then chooses the first entry.

4
  • I can guarantee that it works. Empty output means the name in A column ( name column) which corresponds to the MAX in E column is empty. Try just the SORT equation. =SORT('Form Responses 1'!A:E,5,0)
    – TheMaster
    Sep 22 '17 at 15:21
  • 1
    We could assume that source of the data is a Google Form and in such case the first row contains the column labels and the first column contains timestamps. If we agree on this assumptions, then some slights changes should be made to the formula.
    – Rubén
    Sep 22 '17 at 15:40
  • @Rubén, Like: =INDEX(SORT('Form Responses 1'!A2:E,5,0),1,2) Skips the header row and chooses names in B column...
    – TheMaster
    Sep 22 '17 at 15:43
  • @Rubén and pnuts Appreciate the inputs. Edited my answer 👍
    – TheMaster
    Sep 22 '17 at 15:56

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.