0

I am becoming more and more familiar with Excel's new array functionality and absolutely love the direction it is all headed.

I've been trying to find a way to have a single formula which will spill an array of multiple columns, each which correspond to the proper SKU in column A.

In order to achieve this currently, I just write a formula like this, then I drop the formula down to the bottom of the data.

=XLOOKUP($A2,SKUP[SKU],HSTACK(SKUP[SKU Desc],SKUP[Package Desc],SKUP[Supplier]),"Not Found",0)

I just recently learned about LAMBDA formulas and trying to learn more about them. I was wondering if there is any way I could change this formula to reference all SKUs in column A, instead of having to use just 1 SKU at a time for this calculation?

I was thinking of something like this:

=LAMBDA(sku_array,LET(r,ROW(),s,INDEX(sku_array,r),XLOOKUP...

However, each attempt I've tested with something similar just seems to give errors. If I change the original XLOOKUP formula making the search value the whole column, it still only results 1 column.

ZygD
  • 22,092
  • 39
  • 79
  • 102
Andy L
  • 93
  • 6
  • 1
    `=XLOOKUP($A$2:$A$100,SKUP[SKU],HSTACK(SKUP[SKU Desc],SKUP[Package Desc],SKUP[Supplier]),"Not Found",0)` No need for LAMBDA. – Scott Craner Nov 03 '22 at 21:02
  • Hey @ScottCraner that doesn't work unfortunately, when you type it that way, it only results the first column (Just tested, only provides SKU Desc, does not include other columns) – Andy L Nov 03 '22 at 21:05
  • 1
    Ah yes try this: `=MAKEARRAY(ROWS($A$2:$A$100),3,LAMBDA(x,y,XLOOKUP(INDEX($A$2:$A$100,x),SKUP[SKU],CHOOSE(y,SKUP[SKU],HSTACK(SKUP[SKU Desc],SKUP[Package Desc],SKUP[Supplier]),"Not Found",0)))` – Scott Craner Nov 03 '22 at 21:10
  • Or you could HSTACK 3 XLOOKUPS: `=HSTACK(XLOOKUP($A$2:$A$100,SKUP[SKU],SKUP[SKU Desc],"Not Found",0),XLOOKUP($A$2:$A$100,SKUP[SKU],SKUP[Package Desc],"Not Found",0),XLOOKUP($A$2:$A$100,SKUP[SKU],SKUP[Supplier],"Not Found",0))` – Scott Craner Nov 03 '22 at 21:32
  • 1
    Why not just `HSTACK` first then `FILTER` the result? `=FILTER(HSTACK(SKUP[SKU Desc],SKUP[Package Desc],SKUP[Supplier]),ISNUMBER(MATCH(SKUP[SKU],A2:A100,0)))` – Jos Woolley Nov 04 '22 at 05:57
  • @ScottCraner I'll have to look into your first recommendation, definitely something I'd love to learn the functionality of. Thanks for the tips – Andy L Nov 04 '22 at 13:52
  • @JosWoolley I think your idea is going to be best, especially because within that HSTACK I could add other calculations if needed. Thanks for the tip, wish you would've posted an answer instead of a comment so I could mark it. – Andy L Nov 04 '22 at 13:53

1 Answers1

0

Why not just HSTACK first then FILTER the result?

=FILTER(HSTACK(SKUP[SKU Desc],SKUP[Package Desc],SKUP[Supplier]),ISNUMBER(MATCH(SKUP[SKU],A2:A100,0)))

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9