Forum Discussion

lori_m's avatar
lori_m
Iron Contributor
Dec 17, 2024

Interesting scenario and method. 

I played around a little looking to see if it was possible to avoid the use of thunks by combining references into arrays and had partial success. If the names of the ranges are Table1,..,Table4 we can define a multi-area range: Tables=Table1,Table2,Table3,Table4 and try the following formula to convert this to an array of ranges. 

=SUMIF(LAMBDA(T,MAP(SEQUENCE(AREAS(T)),LAMBDA(a,DROP(INDEX(T,,,a),{0}))))(Tables),"<>")

Strangely, this appears to work fine in Monthly Enterprise Channel but not Beta Channel.

An alternative that was more successful was to build a multi-area reference via REDUCE as shown

 

 

lori_m's avatar
lori_m
Iron Contributor
Dec 23, 2024

Related to the DICTIONARYλ suggestion, I wonder if it might be worth defining a nestable dictionary such as, 

DICT = LAMBDA(array, LAMBDA([key],
    IF(ISOMITTED(key),
        array,
        XLOOKUP(key, VALUETOTEXT(TAKE(array, , 1)), TAKE(array, , -1))
        )
    )
);

which, like in python, returns a dictionary from a two column array. e.g. one could try

=countries()
=countries("United Kingdom")() 
=countries("France")("Paris")

where

countries =DICT(
    WRAPROWS(VSTACK(
       "United Kingdom", DICT(Table1),
       "Germany", DICT(Table2),
       "France", DICT(Table3),
       "Italy", DICT(Table4)
   ),2)
);

Though I'd still tend to side with the earlier PIVOTBY suggestion. 

Resources