Find names in string and combinations & lookup for value - Google Sheets

Question

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: https://docs.google.com/spreadsheets/d/1leb0hQ5gb6RclcMb__JYJLqvIFBQD_B3v7c2o4PbICg/edit#gid=0

I tried lookup, but it is fulling first case but not the people combination. Is there any way I can achieve this. Also, I assume these names are separated by ascii CHAR(10).

Let me know any inputs or suggestions so I can fulfil this.

Answer 1

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))))))))

Answer 2

You may use below formula-

=IFERROR(BYROW(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,UNIQUE(INDEX(XLOOKUP(FLATTEN(SPLIT(x,CHAR(10))),E2:E7,F2:F7,,0))))),"C")

• Here A2:INDEX(A2:A,COUNTA(A2:A)) will return a array of values as well cell reference from A2 to last non empty cell in column A (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this post by @TheMaster

• Then LAMBDA() will apply XLOOKUP() function for each cell of A column.

• SPLIT() will separate all values from cell using line break CHAR(10) delimiter.

• XLOOKUP() will search for name and return team.

• For multiple names, UNIQUE() function will return only unique teams.

• Finaly BYROW() will return that unique team. If there are more than 1 teams, then BYROW() will through error.

• IFERROR() will catch that error and return C as result/exception.