I am a bit new to excel and have a spreadsheet where I am trying to use the MAX of a range of cells in order to return the name of the row that that MAX belongs to. So, I have a cell returning the MAX of 8 different ranges (i.e. =MAX(B2:B10,C2:C10,D2:D10,...)). Now, in let's say cell A2:A10, I have the names of each set of rows listed previously. I want a function that takes whatever the MAX of those ranges is, checks what name or "category" of rows that MAX belongs to, and then returns that name. If that isn't clear I am trying to use these two things to find the record for a weekly score (the MAX) and then in another cell return the name of that recordholder. I have tried using IF and COUNTIFS functions to try to check the value in the MAX cell against the names but cannot wrap my head around how to get it to return the name. Let me know if I explained this poorly, first time trying to post a question here. Any help would be appreciated!
1 Answer
Using the sheet in your example enter:
in cell B12 as an array formula (by pressing Ctrl-Shift-Enter) =SUMPRODUCT((B2:D10=MAX(B2:D10))*ROW(B2:D10))-ROW(B2:D10)+1)
If you entered it correctly as an array formula then it should appear in the formula bar like: {=SUMPRODUCT((B2:D10=MAX(B2:D10))*ROW(B2:D10))-ROW(B2:D10)+1)}
Then in cell B13 enter: =INDEX(A2:A10,B12,1)
That should give you the value you seek
-
1
-
-
Great - if you are happy please accept the answer as the solution so others with the same question can find it. Happy to have helped.– FalstoneCommented May 25, 2021 at 8:06
-
Do you have an extra close paren at the end? Also, If there are ties, this will throw an error. Perhaps replace SUMPRODUCT with MAX or MAXIFS. Commented May 26, 2021 at 14:38