SOLVED

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

Steel 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!

5 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

best response confirmed by DKoontz (Steel Contributor)
Solution

# 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.

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

Ah okay that's what I suspected. Thank you for providing a link to the array of array using lambda!

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

Great examples! Thank you so much for taking the time to provide both of these solutions, both achieve the same result. The index method is a little less intimidating but it looks like the Lambda solution has way more flexibility, so many functions within it that I've never even heard of. Really cool. Gave me some good homework, thanks.

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

You can use REDUCE with XLOOKUP:

Syntax:

``````=DROP(
REDUCE(
"", lookup_value,
LAMBDA(accr,val,
VSTACK(accr,XLOOKUP(val,lookup_array,result_array,{"","","",""}))
)
),1
)``````

Note: {"","","",""} is the if_not_found which should match the number columns in the result_array.

Source: ExcelMee

1 best response

Accepted Solutions
best response confirmed by DKoontz (Steel Contributor)
Solution

# 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.