Return value if a cell in the same row has highest value in the column
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.
Answers
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!