Index Collect Contains Multi Dropdown and Text String

paige_pj

I am trying to pull a Name based on 2 criteria in 1 sheet into another sheet:

Assigned References:
{Name} - Sheet 1

{Role} - Sheet 1 - Two Options; Primary and Liaison

{Client Assignment} - Sheet 1 (is in a multi-drop down)

[AssignedClient] - Sheet 2 Row to match

[Name] - Sheet 2 pulling based on Primary or Liaison

I've tried several variations of this:

=INDEX(COLLECT({Name}, {Role}, "Primary", {Client Assignment}, CONTAINS([AssignedClient]@row), 1))

=INDEX(COLLECT({Name}, {Client Assignment}, [AssignedClient]@row, {Role}), ="Primary", 1)

=IF({Role} = "Coach", INDEX(COLLECT({Name}, {Client Assignment}, HAS(@cell, [AssignedClient]@row), 1)))

=INDEX(COLLECT({Name}, {Client Assignment}, CONTAINS([AssignedClient]@row, @cell), {Client Assignment}, CONTAINS("Primary", @cell), {Role}), 1)

=INDEX(COLLECT({Name}, {Client Assignment}, HAS([AssignedClient]@row, @cell), {Client Assignment}, CONTAINS("Primary", @cell), {Role}), 1)

I feel like I'm being tripped up by how to indicate that the {Role} reference is supposed to be a text string, but with help, I've done a multi-criteria index collect before.

paige_pj

Thank you for this amazing summary! This equation worked with a tweak back to the text string!

For others with the same question, we do not have the role repeated in Sheet 2. That is why I was trying to match to a text string of "Primary" or "Liaison". Sheet 2 would be like this, so I would be using Sheet 1 and the [AssignedClient]@row to pull out the Primary and Liaisons from Sheet 1 into Sheet 2.

So, I don't have the Role@row here. I used the text instead!
=JOIN(COLLECT({Name}, {Role}, "Primary", {Client Assignment}, HAS(@cell, AssignedClient@row)), CHAR(10))

WHOOHOOO!