Formula for Max Value and Row Name
Hello 
I am trying to pull in the max value of column A and the name(s) associated with that value.
I am trying to use the following formula to pull in the max value and taking into consideration there could be multiples of the max value:
=LARGE(DISTINCT([B]$1:[B]$7), #)
And then I would like to show the value(s) in column A that are tied to the maximum value. I was going to try a "Join" and "Collect" formula but didn't get very far.
Any assistance would be greatly appreciated.
Thanks!
Best Answer

Hey @Justin Tackitt
Your Join/Collect was on the right track, but you need to do it after finding the MAX/DISTINCT
I used the Sheet Summary fields (right hand menu on sheet) to house the formulas. You first find the Max Distinct, then you use that Max value as a criteria in a Join Collect.
I used the entire columns as the ranges, rather than limiting it to 7 rows. You can change the range length if the data set is only 7 rows long. If you want to keep the range dynamic, then use the entire column so the range will grow as the column length grows. The criteria [Max Distinct]# is how a sheet summary field references another Sheet summary field. If you are not using Sheet Summary fields, then you would have the specific cell reference to where your Max/Distinct in that place.
Will this work for you?
Kelly
Answers

Hey @Justin Tackitt
Your Join/Collect was on the right track, but you need to do it after finding the MAX/DISTINCT
I used the Sheet Summary fields (right hand menu on sheet) to house the formulas. You first find the Max Distinct, then you use that Max value as a criteria in a Join Collect.
I used the entire columns as the ranges, rather than limiting it to 7 rows. You can change the range length if the data set is only 7 rows long. If you want to keep the range dynamic, then use the entire column so the range will grow as the column length grows. The criteria [Max Distinct]# is how a sheet summary field references another Sheet summary field. If you are not using Sheet Summary fields, then you would have the specific cell reference to where your Max/Distinct in that place.
Will this work for you?
Kelly

This is perfect!!! Thank you very much for the help!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 438 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 283 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!