LOOKUP for each comma delimited item

[FlashDota]

I want to achieve the following results, How can I do this without VBA, only formulas?

			Output:
Actor	Movie		Result Actor	Desired Result Movies
Actor 1, Actor 2, Actor 3	Oppenheimer		Actor 1	Oppenheimer, Barbie
Actor 1	Barbie		Actor 2	Oppenheimer, Spiderman
Actor 2	Spiderman		Actor 3	Oppenheimer

 

[ExcelToDAX]

I noticed you have 2021 Excel version. By chance have you upgraded to 365? Here is a solution using newer Excel functions assuming your values are in A2:B4...

=LET(
actors,A2:A4,
movies,B2:B4,
a, LET(allActorsMovies,LET(finalVal,IFNA(TOCOL(TEXTSPLIT(CONCAT(actors&"-"),", ","-")&"_"&movies),""),FILTER(finalVal,finalVal<>"")),CHOOSE({1,2},TEXTBEFORE(allActorsMovies,"_"),TEXTAFTER(allActorsMovies,"_"))),
b, CHOOSECOLS(a,1),
c, UNIQUE(b),
d, MAP(c, LAMBDA(x, TEXTJOIN(", ",,FILTER(CHOOSECOLS(a, 2),b=x)))),
e, HSTACK(c, d),
e
)

 

[FlashDota]

I noticed you have 2021 Excel version. By chance have you upgraded to 365? Here is a solution using newer Excel functions assuming your values are in A2:B4...

=LET(
actors,A2:A4,
movies,B2:B4,
a, LET(allActorsMovies,LET(finalVal,IFNA(TOCOL(TEXTSPLIT(CONCAT(actors&"-"),", ","-")&"_"&movies),""),FILTER(finalVal,finalVal<>"")),CHOOSE({1,2},TEXTBEFORE(allActorsMovies,"_"),TEXTAFTER(allActorsMovies,"_"))),
b, CHOOSECOLS(a,1),
c, UNIQUE(b),
d, MAP(c, LAMBDA(x, TEXTJOIN(", ",,FILTER(CHOOSECOLS(a, 2),b=x)))),
e, HSTACK(c, d),
e
)

Thanks for the answer. Nope, still Office 2021. This is returning a #NAME? error