1

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 Tuinstra Jan 15 '14 at 18:49
2
=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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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