INDEX/COLLECT with both MATCH and CONTAINS criteria

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!

Find more posts tagged with

Formulas

collect

INDEX

MATCH

Accepted answers

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)

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.