Find names in string and combinations & lookup for value

I've a data set with list of names & combinations and need to map with the team.

For ex, if the list of name is unique, then it should give me team name.. If the name of combination from team, it should give same team name.. Combination between two teams, then it should say team C.

Here is the trix for ref:

CodePudding user response：

You can try with this formula. It's a little long but splits the value of each cell, joins them to see if they're unique or not, and then applies C if they aren't:

=BYROW(A2:A,lambda(val,IF(val="","",lambda(results,SI(COUNTA(results)=1,results,"C"))(UNIQUE(transpose(ARRAYFORMULA(VLOOKUP (SPLIT(val,CHAR(10)),E$2:F,2,0))))))))