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
    Commented Sep 22, 2017 at 18:35
  • Yes, I would like that.
    – TheMaster
    Commented Sep 22, 2017 at 19:07
  • Sorry, there is already a lot of trash out there and apparently some angst too.
    – rearThing
    Commented Sep 22, 2017 at 20:31

4 Answers 4

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
    Commented Sep 23, 2017 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? :) Commented Sep 22, 2017 at 15:07
  • 2
    @pnuts Maybe someone found helpful to know that your answer is helpful :) Commented Sep 22, 2017 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. Commented Sep 22, 2017 at 15:34
  • Thanks, Ruben, that makes a lot of sense. Cant vote up yet...
    – rearThing
    Commented Sep 23, 2017 at 14:32
  • 1
    Rubén: Right, spot on, upvoting @pnuts :-)
    – ttarchala
    Commented Sep 25, 2017 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
    Commented Sep 22, 2017 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. Commented Sep 22, 2017 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
    Commented Sep 22, 2017 at 15:43
  • @Rubén and pnuts Appreciate the inputs. Edited my answer 👍
    – TheMaster
    Commented Sep 22, 2017 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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