Is XLOOKUP Able to Spill In Two Dimensions at Once?

Frequent Contributor

I was trying earlier to get an XLOOKUP to spill two ways at once off of a dynamic range, I could spill out right or down, but was unable to get it to spill both ways. I ended up just filtering the entire array (way simpler to begin with), but am still curious if this is possible or a limitation of XLOOKUP.

 

Quick example:

DKoontz_0-1641848621868.png

Showing the dynamic array in column F:F

 

DKoontz_1-1641848635589.png

XLOOKUP on each individual part works correctly, returning Size:Shape

 

DKoontz_2-1641848645525.png

But, XLOOKUP on the dynamic array in F10#, only spills down, returning Size and doesn't spill right at all. 

 

Has anyone had experience with this? I may also be missing a key piece here. Thanks!

 

 

2 Replies

@DKoontz 

You try to return array of arrays which is not supported. That's with lambda and helper function.

 

Here is related sample Array of arrays using Lambda helper functions - Microsoft Tech Community

@DKoontz 

The good news is that XMATCH/INDEX will work and returns an array,

= INDEX(
    Data[[Size]:[Shape]], 
    XMATCH(filtered#,Data[Part]),
    {1,2,3}
  )

XLOOKUP can be made to work but you would need to be desperate or extraordinarily masochistic to attempt it!

= LET(
    THUNK, LAMBDA(x, LAMBDA(x)),
    EXTRACT, LAMBDA(thunkArray, LAMBDA(r,c, INDEX(INDEX(thunkArray,r,1)(),c))),
    thunkTable, BYROW(Data[[Size]:[Shape]], THUNK),
    thunkSelected, XLOOKUP(filtered#, Data[Part], thunkTable),
    MAKEARRAY(ROWS(thunkSelected), 3, EXTRACT(thunkSelected))
  )

Currently you would also need an insider beta version of Excel.