Jan 10 2022 01:10 PM
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!
Jan 10 2022 02:08 PM - edited Jan 10 2022 02:10 PM
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
Jan 10 2022 03:19 PM
SolutionThe 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.
Feb 01 2022 01:57 PM
Feb 01 2022 02:03 PM
Oct 16 2023 07:48 PM - edited Oct 16 2023 07:53 PM
Jan 10 2022 03:19 PM
SolutionThe 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.