Is XLOOKUP Able to Spill In Two Dimensions at Once?

[DKoontz]

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]

@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

[#3 · Accepted solution]

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

[#4]

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

[#5]

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.

[#6]

@DKoontz 

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