Forum Discussion
Find the row that has the maximum value across a number of columns
You may use an array formula (entered with Ctrl+Shift+Enter) similar to B16 of the attached file, which is:
=LOOKUP(2,1/(MMULT(B$2:J$11,
TRANSPOSE(B$1:J$1^0*(B$1:J$1>=B$13)*(B$1:J$1<=B$14)))=B$15),
A$2:A$11)
Note that Column K was presented therein for the sole purpose of verifying the correctness of the result obtained from the foregoing formula.
Hi Twifoo
I love the formula, but I have a minor problem in that my data is in a table, so the headers are actually headers and not just cells, so the first part of your formula, that equals 42, returns 0 (zero). I tried changing B$1:J$1 to Table2[[#Headers],[1]:[52]], but that doesn't work and returns 0 (zero) too. Any ideas?
In the attached version for the file, I converted A1:J11 as Table1. Column L and B15 are presented for verification purposes only. Thus, the revised array formula in B16 is:
=LOOKUP(2,1/(FREQUENCY(0,
1/(1+MMULT(Table1[[1]:[9]],
TRANSPOSE(Table1[[#Headers],[1]:[9]]^0*
(--Table1[[#Headers],[1]:[9]]>=B$13)*
(--Table1[[#Headers],[1]:[9]]<=B$14)))))),
Table1[Name])- My suggested formula was a sample. Please attach your sample file for the specifics.
In a table, the numbered weeks will actually be text. You therefore need either convert the 'To' and 'From' values to text or, conversely, convert the header row to values.
My programming style is not the same as Twifoo 's so I defined a name 'mask'
= (VALUE(Table1[#Headers])>=From) * (VALUE(Table1[#Headers])<=To)
which I use to filter out unwanted columns. My formula to sum across rows
= MMULT(Table1, TRANSPOSE(mask) )
gives the 'score'.
From there, I part company with Twifoo and, I expect, you because I am using the latest Office 365 version. I simply sorted the names descending by score
= @ SORTBY( Name, score, -1 )
and returned the first value only.
PeterBartholomew1I am using the latest version of Office 365 too, but SCORE is not a field in my data.
