Google-sheets – google spreadsheet filter, string equivalent

google sheets

Columns D, E, and F store names that represent 2-3 person teams assigned to the name in column A. The teams are assigned to multiple names and thus appear in multiple rows. The teams' composition stays the same and is composed of names from column A.

I need to populate columns B and C with the other members of the team the person in column A is assigned to in D2:F. Im playing with query() and filter(), but am not making headway. I think filter is better due to potential column order changes.

What is the correct formula to use and can you break down the individual components? Note: Columns A:F do not contain raw data, but formulas in the real data.

Example spreadsheet to check out.

Best Answer

=iferror(index(D:F,match(A2,D:D,0),2),index(D:F,match(A2,E:E,0),1))  
=iferror(index(D:F,match(A2,D:D,0),2),index(D:F,match(A2,E:E,0),1))

in B2 and C2 respectively and copied down, matches the example output you did provide and I think properly covers all the others except frank when participant rather than a team member.

However the principle could be extended to cover frank also.

Related Topic