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

     

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/ 

Resources