Return value if a cell in the same row has highest value in the column

SarahBKarna

I'm working on an email campaign and want to see which issue has the highest open rate. If a value in Column C has the highest value in the column, return the value of Column A in the same row. I'm stumped. Can anyone provide this formula? VLOOKUP seems like the obvious choice, but I don't know how to reference a MAX formula.

Dave Stanley

@SarahBKarna I'm not 100% sure what you are trying to do but this formula may do the trick:

=INDEX(COLLECT(ColumnA:ColumnA, ColumnC:ColumnC, MAX(ColumnC:ColumnC)), 1)

This returns the value in ColumnA where the value in ColumnC is the maximum value found for all items. In the example below since 10 is the largest value the result returned will be "B".

You could also do this with a VLOOKUP():

=VLOOKUP(MAX(ColumnC:ColumnC), ColumnC:ColumnA, 2, 0)

But this would require that ColumnC is the leftmost column in your range so that the lookup has something to search for. Basically find the maximum value ("10") and use that to find the correct row and return the value from ColumnA.