Formula for Max Value and Row Name

Justin Tackitt

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!

Kelly Moore

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