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:


Showing the dynamic array in column F:F



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



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


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


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


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

= LET(
    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.