Forum Discussion
PeterBartholomew1
Sep 01, 2021Silver Contributor
A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
I remember JoeMcDaid stating that 2D arrays are the only data types held in Excel; rows columns and scalars are merely special cases with the last being a 1x1 array. So why is it such a proble...
PeterBartholomew1
Sep 02, 2021Silver Contributor
Thank you each for contributing to my continuing education!
At first I was distracted by the MAKEARRAYs but then realised the answer lay in the use of MAP. I guess the key to generating a 2D array is that the array parameters must be of the same shape.
= LET(
teamList, UNIQUE(team),
teamScore, MAP(teamList,SCOREλ),
teamOrder, SORTBY(teamList,teamScore),
team, IF({1,1,1,1},teamOrder),
place, IF(ISTEXT(teamOrder),{1,2,3,4}),
MAP(team, place,
LAMBDA(t,p,INDEX(MEMBERSλ(t), p))
)
)
I have shortened the worksheet formula by moving the formulae that extract arrays from the original rawdata to global defined names along with the definition of the Lambda functions.