# Is XLOOKUP Able to Spill In Two Dimensions at Once?

Frequent Contributor

# Is XLOOKUP Able to Spill In Two Dimensions at Once?

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

# Re: Is XLOOKUP Able to Spill In Two Dimensions at Once?

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

# Re: Is XLOOKUP Able to Spill In Two Dimensions at Once?

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.