How to use Lambda in Excel to return array of results for an array

Question

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.

Answer 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)))