Forum Discussion
Find the row that has the maximum value across a number of columns
I have a table that has columns which represent week numbers and rows which represent people. The cells have a number in for each week against each person. What formula can I use to find out the pers...
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])