Dynamically spilling a function in Excel - matching value on array outside the lambda

Question

I know how to create formulas in Excel that select values on a join key (example in image below).

However, I would like to be able to create one formula that replicates this function for the whole array of interest, without knowing the size of the array beforehand.

I have tried BYROW, but unfortunately, I cannot pass the array on the left into the lambda function. What solutions do I have here?

I have tried BYROW, and I was expecting to spill the function along the rows. However, I could not pass the join array into the lambda function.

Answer 1

Lookup Data: Return Multiple Matches in Rows

Given

=LET(data,B3:C22,uCol,1,vCol,2,uData,E3:E5,if_not_found;"";
    ud,CHOOSECOLS(data,uCol),
    vd,CHOOSECOLS(data,vCol),
IFNA(DROP(REDUCE("",uData,LAMBDA(rr,r,
    VSTACK(rr,TOROW(FILTER(vd,ud=r,if_not_found))))),1),""))

All

=LET(data,B3:C22,uCol,1,vCol,2,
    ud,CHOOSECOLS(data,uCol),
    vd,CHOOSECOLS(data,vCol),
    u,UNIQUE(ud),
HSTACK(u,IFNA(DROP(REDUCE("",u,LAMBDA(rr,r,
    VSTACK(rr,TOROW(FILTER(vd,ud=r))))),1),"")))

• Keep in mind that the order is determined with u i.e. you could e.g. use u,SORT(UNIQUE(ud)),.