Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
Dec 14, 2024

This appears to be a more flexible XLOOKUP/Thunk demo.  I recall a workbook from a few years ago where XLOOKUP was being used to unpack thunks and return tables.  The catch was the tables had to be VSTACK'd and thunked - every one of them.  It made for an exciting demo but would be difficult to find a practical use for the technique.  Nevertheless, I studied it and made my own workbook until I could internalize it.

A few observations:

  1. I think what is fascinating with this workbook is how you've managed to avoid converting ranges to arrays.  TAKE/DROP are interesting because the result of each function is accepted by the range-only functions.  The part I wouldn't have guessed would hold up is when countryArrϑ is unpacked with XLOOKUP then later used in COUNTIFS!
  2. There's no declared Thunk function in your module but I see where countryArrϑ is being thunked.  Very subtle and smooth.
  3. I messed around a bit with trying SelectDataλ with 3D references but received errors because I used VSTACK on my references and made arrays out of ranges. Could this function handle 3 references? 

     

 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 14, 2024

    In a sense, the array of 2D ranges down the sheet is a representation of a 3D range.  If we turn attention to 3D ranges of the form

    range3D
= Sheet1:Sheet3!$K$3:$K$20

    I suspect that life is going to get a whole lot trickier.  

    It should be possible to return an array of thunked arrays, but that would not allow the use of TRIMRANGE or any of the xIFS functions.

    = LET(
    TRIMARRλ, LAMBDA(column, LAMBDA(FILTER(column, NOT(ISBLANK(column)),""))),
    stacked,  HSTACK(range3D),
    thunkArr, TOCOL(BYCOL(stacked, TRIMARRλ)),
    MAP(thunkArr, LAMBDA(ϑ, AVERAGE(ϑ())))
  )

    would apply the AVERAGE function to each sheet individually.  Another option might be to use INDIRECT which can return ranges.  For example

    =INDEX(
    MAP(
        TOCOL(Sheet1:Sheet3!$A$1) & "!K3:K20",
        LAMBDA(string,
            LAMBDA(TRIMRANGE(INDIRECT(string)))
        )
    ),
    2,
    1
)( )

    returns the trimmed range on sheet 2. 

    Note: The range Sheet1:Sheet3!$A$1 contains the sheet names

    = TEXTAFTER(CELL("filename",A1), "]")

    Nothing too elegant but, as yet, we are simple sounding out the limits of what is possible, though I concede that may be limited by my lack of understanding.

Resources