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.

  • Please, share a doc with us and show us the expected result (written down).
    – Jacob Jan
    Jan 15, 2014 at 18:49

1 Answer 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.