Snasui Excel Forum

Hi there,

I need your help to concatenat cells conditionally on the basis of multiple criteria.i don't have dynamic array and Textjoin without vba.what formula can I do?

Best wishes

Ex.
J2 =INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13=$G$1,IF($C$2:$C$13=$G$2,ROW($A$2:$A$13)-ROW($A$1))),COLUMNS($J$1:J$1)))
Enter & Copy to L2

H2 =LEFT(IF(J2="","",$J2&",")&IF(K2="","",$K2&",")&IF(L2="","",$L2&",")&IF(M2="","",$M2&",")&IF(N2="","",$N2&",")&IF(O2="","",$O2&","),LEN(IF(J2="","",$J2&",")&IF(K2="","",$K2&",")&IF(L2="","",$L2&",")&IF(M2="","",$M2&",")&IF(N2="","",$N2&",")&IF(O2="","",$O2&","))-1)

Noted (For textjoin)
=TEXTJOIN(",",TRUE,IF($B$2:$B$13=$G$1,IF($C$2:$C$13=$G$2,$A$2:$A$13,""),""))

thank you