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
Check out the Formula Handbook template!