yogi_Return Offset Cell Value Based On Maximum Value In A Data Range

Google Spreadsheet   Post  #2198

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-04-2017

question by CRODIS:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Gzkvkb9GM1E;context-place=forum/docs

Returning offset cell value based on maximum value in a data range

That was the best title I could come up with, sorry :(

I'm trying to find the maximum value in a particular unsorted data range, then return the offset of a value of any equal cells.

..that's still a terrible explanation, so here's an example:

I want cell O6 to simply be, "=MAX(B7,E7,H7,K7,B17,E17,H17,K17)" but then I want cell N6 to take each Cell in that same range that returns the same value as cell O6 will display, then return a list of people that have that value in that range. So if, for example, the highest value in the unsorted range was a 12, and cells E7 and E17 both had that value, I'd want cell O6 to simply say "12," and N6 to say, "Person 2, Person 6" since those are the offset values I'd want to use to populate the list.
God I hope that makes sense.

One additional point: I want to preserve the formatting if at all possible; if it's absolutely necessary to use a sorted list, I will, but definitely prefer a solution that lets me leave it unsorted.

THANK YOU!