Forum Discussion
Solved
Formula to Identify Winning Team by Team Name Over Different Days and Different Events
Hi All, Need help with this one. I'm trying to find the winning team that wins specific events on specific days based on LOWEST score. They should be identified by name (Team 1, Team 2, etc.. etc...
=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.
I have achieved the desired result using power query.
check the attached worksheet.
Just put your complete data in "A2" cell of INPUT sheet and then refresh the query in RESULT sheet.
let me know if you face any issue.