Forum Discussion
Formula to Identify Winning Team by Team Name Over Different Days and Different Events
=LET(rng,B3:M7,
DROP(REDUCE("",SEQUENCE(1,3),
LAMBDA(a,b,HSTACK(a,REDUCE("",SEQUENCE(ROWS(rng)),
LAMBDA(u,v,
LET(z,CHOOSECOLS(WRAPROWS(CHOOSEROWS(rng,v),3),b),VSTACK(u,
TEXTJOIN(", ",,FILTER(CHOOSECOLS(WRAPROWS(B1:M1,3),b),IF(z>0,z)=MIN(IF(z>0,z)))))))))))
,1,1))
This formula which spills the result could be an alternative. In my example i don't work with merged cells in row 1 in order to avoid potential problems.
Adjust the last column M for your real setup.
For day 1:
=LET(d, 1, results, IF(($B$2:$M$2="Day"&d)*($B3:$M3>0), $B3:$M3), m, MIN(results), pos, MATCH(m, results, 0)-d+1, INDEX($B$1:$M$1, pos))
For day 2:
=LET(d, 2, results, IF(($B$2:$M$2="D"&d)*($B3:$M3>0), $B3:$M3), m, MIN(results), pos, MATCH(m, results, 0)-d+1, INDEX($B$1:$M$1, pos))
For day 3:
=LET(d, 3, results, IF(($B$2:$M$2="D"&d)*($B3:$M3>0), $B3:$M3), m, MIN(results), pos, MATCH(m, results, 0)-d+1, INDEX($B$1:$M$1, pos))
