Forum Discussion

DKoontz's avatar
DKoontz
Iron Contributor
Jan 10, 2022
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 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!

 

 

excel
Formulas and Functions
  • PeterBartholomew1's avatar
    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.

     

5 Replies

  • excelmee's avatar
    excelmee
    Copper Contributor
    Oct 17, 2023

    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: https://excelmee.com/excel-formulas/lambda/spill-xlookup-results-in-2-dimensions-in-excel/ 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    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.

     

    • DKoontz's avatar
      DKoontz
      Iron Contributor
      Feb 01, 2022
      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.
    • DKoontz's avatar
      DKoontz
      Iron Contributor
      Feb 01, 2022
      Ah okay that's what I suspected. Thank you for providing a link to the array of array using lambda!

Resources