Referencing to cell value

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.

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

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.

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

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

@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))
• If you found pnuts' answer as a good answer why it doesn't has any vote? :)
– Rubén
Sep 22 '17 at 15:07
– Rubén
Sep 22 '17 at 15:14
• @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... Sep 23 '17 at 14:32
• Rubén: Right, spot on, upvoting @pnuts :-) Sep 25 '17 at 21:50

@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.

• 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) Sep 22 '17 at 15:21
• 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... Sep 22 '17 at 15:43
• @Rubén and pnuts Appreciate the inputs. Edited my answer 👍 Sep 22 '17 at 15:56