Complex Lookup

Steve Carter · 03/25/25

I have a main table that, given the team(s) involved in a project, needs to look up the contact info for members of all of those teams. ([Teams Involved] is a multiselect dropdown, and [Members of Involved Teams] is a contact list where multiple contacts are allowed.)

It is to get the list of team members from a lookup table like this:

If [Teams Involved] were not multiselect, I could simply use an INDEX(MATCH( formula; but since multiple teams can be selected, that doesn't work.

I have tried multiple formulas:

=(INDEX(COLLECT({TeamMemberNames}, {TeamNames}, HAS(@cell, [Teams Involved]@row)), 1)) works great if there is only one team chosen, but give an error when there is more than one.

=INDEX(COLLECT({TeamMemberNames}, {TeamNames}, HAS([Teams Involved]@row, @cell)), 1) pulls the contacts, but only for the first team on the list. I can get the second team's members by changing the last argument of the INDEX function to 2, but I need to get all the teams' members, not just team by team.

I tried concatenating a series of the above formula, but that outputs a text list of the contacts' names rather than actual data of the contact list type. In other words, =INDEX(COLLECT({TeamMemberNames}, {TeamNames}, HAS([Teams Involved]@row, @cell)), 1) + INDEX(COLLECT({TeamMemberNames}, {TeamNames}, HAS([Teams Involved]@row, @cell)), 2) yields this comma separated list: person1, person2, person1, person3, person4. And, of course, this approach doesn't address the fact that I don't know how many teams might be selected.

So I clearly need some additional function that can combine the data retrieved by the COLLECT function and keeps it as contact data rather than converting it to text.

Anybody know what that function is?

Many thanks for any help you can provide!