Today we are releasing to our Beta customers a new capability that will revolutionize how you build formulas in Excel. Excel formulas are the world’s most widely used programming language, yet...
I am gradually getting used to building solution with the focus upon λ-functions. In the picture shown (from a question on the Chandoo forum) the challenge was to pick the competitors with the 3 highest scores. Traditional formulas are quite possible but the the use of λ-functions provides clarity of intent; the detail is hidden from view.
Here, I have used one function (UNPIVOTTED) to unpivot the table to a 2-column list, then the second (TOP) sorts the list by the second column and returns the top 3 records.
For completeness, UNPIVOTTED & TOP refer to
= LAMBDA(data,hdr,
LET(
m, ROWS(data),
n, COLUMNS(data),
k, SEQUENCE(m*n,1,0),
r, 1+QUOTIENT(k,n),
c, 1+MOD(k,n),
u, INDEX(data,r,c),
h, INDEX(hdr,c),
CHOOSE({1,2},h,u) )
)