Can a formula return corresponding values from one column into another, removing blanks and separating them with commas?

Question

I have a sheet with the following setup:

In cell K2, I use this formula:

=IF(K1="","",IF(XLOOKUP(K1,H:H,I:I)=0,"",TRANSPOSE(FILTER(B1:F1,ISNUMBER(SEARCH(TRIM(K1),QUERY(B2:F,,ROWS(B2:F))))))))

This formula does the following:

1. Checks if K1 is not blank.
2. Searches the dataset B2:F for the value in K1.
3. Returns the corresponding results from B1:F1, removing blanks.
4. Ensures that the value in column I is not 0.

I want to extend this formula to also return the corresponding values from column A2:A and display these results in L2:L, separated by commas if there are multiple matches.

Is it possible to modify the existing formula to include this, or would it be better to use a separate formula in L2?

Example of expected results:

Link to spreadsheet: https://docs.google.com/spreadsheets/d/1tprQIYEMgLRuyCMCQ0cDO_JDzWKdW-eTyXH76HJTeic

Answer 1

Here's a generalized approach which you may adapt accordingly:

=let(Λ,transpose(bycol(B2:F,lambda(Σ,if(xmatch(K1,Σ),vstack(index(1:1,column(Σ)),join(", ",filter(A2:A,Σ=K1))))))),
 filter(Λ,index(Λ,,2)<>""))