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

excel

Formulas and Functions

PeterBartholomew1

Jan 10, 2022

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.

XLOOKUP Array of arrays.xlsx16 KB

Forum Discussion

Is XLOOKUP Able to Spill In Two Dimensions at Once?

Resources