Google Sheets - TEXTJOIN with multiple Criteria-CodePudding

I'm looking to give out a textjoin of multiple, horizontal cells, depending on three criteria:

I want the function to find the email from column E in the sheet called "UGC_Product_Pick" --> If it also shows A1(test_project) in column X, as well as A2 (test_client) in column Y --> TEXTJOIN the info from columns B, C And D within this row. I hope this makes sense.

I tried a couple different approaches. The one that has worked best up to this point is: IF AND with TEXTJOIN. But for some reason the IF function won't find the email, even though it's right there. Also the TEXTJOIN will only give out the info from all rows, not only the one matching all the criteria.

Can anyone help?

=IF(AND(UGC_Product_Pick!Y:Y=A1;UGC_Product_Pick!X:X=A2;UGC_Product_Pick!W:W=E4);TEXTJOIN(", ";TRUE;UGC_Product_Pick!B:B;UGC_Product_Pick!C:C;UGC_Product_Pick!D:D);"-")

CodePudding user response：

try:

=FLATTEN(QUERY(TRANSPOSE(FILTER(UGC_Product_Pick!B:D; 
 UGC_Product_Pick!Y:Y=A1;
 UGC_Product_Pick!X:X=A2;
 UGC_Product_Pick!W:W=E4));;9^9)))

update:

=INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(FILTER(
 IF(UGC_Product_Pick!B:D="";;UGC_Product_Pick!B:D&","); 
 UGC_Product_Pick!Y:Y=A2;
 UGC_Product_Pick!X:X=A1;
 UGC_Product_Pick!W:W=E4));;9^9))); ",$";))