Join range of row using arrayformula

Question

I have a range of data on column A and B. In Column D , i have a reference for the shotID. I want to make a list for the artist involved for specific shotID.

In E2 i use this :

=JOIN( "," , FILTER($B$2:$B, $A$2:$A= D2))

then copy down to E3,E4. It works as i expected, but i want to do it using array formula. So only use single formula in E2 and that doesn't work that simple :

=arrayformula( JOIN( "," , FILTER($B$2:$B, $A$2:$A= D2:D4)) )

How can i do this ?

Answer 1

One more possibility that I learned from player0 and surprised he didn't suggest...

=ARRAYFORMULA(SPLIT(TRANSPOSE(SUBSTITUTE(TRIM(QUERY(QUERY(A2:B&{"|",CHAR(10)},"select MAX(Col2) where Col1<>'|' group by Col2 pivot Col1"),,100)),CHAR(10),",")),"| ",0))