Forum Discussion
Solved
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...
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.
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.
- 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.
