INDEX/COLLECT with both MATCH and CONTAINS criteria

Mandy and Jen

I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula.

Source Sheet:

Destination Sheet:

In the Attending School column in Destination Sheet, I'd like to populate with the source sheet School value if the Decision contains "Attend" AND the names in both sheets match.

I was able to get the CONTAINS part working with this:

=INDEX(COLLECT({Source SCHOOL}, {Source DECISION}, CONTAINS("Attend", @cell)), 1)

But I don't know how to add in the MATCH portion without an error. Thanks for your help!

Michelle Choate 2

COLLECT works like a MATCH function with multiple criteria, so you would just add the additional criteria.

=INDEX(COLLECT({Source SCHOOL}, {Source DECISION}, CONTAINS("Attend", @cell), {Source NAME}, Name@row), 1)

Michelle Choate 2

COLLECT works like a MATCH function with multiple criteria, so you would just add the additional criteria.

=INDEX(COLLECT({Source SCHOOL}, {Source DECISION}, CONTAINS("Attend", @cell), {Source NAME}, Name@row), 1)

Mandy and Jen

Thank you @Michelle Choate 2 - that worked!