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.