Index Collect Contains Formula

Hi, I'm trying to report data in one cell if data in another cells contains a partial phrase listed in a third cell. Here's what I've got:

=INDEX(COLLECT({WE Org Owner}, {Pilot/PM}, CONTAINS([Primary Column]@row, @cell)))

It's giving me #incorrect argument. The reason I need a Contains is because the primary column is referencing someone's name, but in Pilot/PM he's listed as Person 1/Person 2. If Person 1 appears in Pilot/PM, I want it to report out the data from WE Org Owner for that row.

What am I missing?

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Nick Allgauer

Looks like you're just missing the row index portion of the index function!

Try this:

=INDEX(COLLECT({WE Org Owner}, {Pilot/PM}, CONTAINS([Primary Column]@row, @cell)), 1)

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Nick Allgauer

Looks like you're just missing the row index portion of the index function!

Try this:

=INDEX(COLLECT({WE Org Owner}, {Pilot/PM}, CONTAINS([Primary Column]@row, @cell)), 1)

Cheers,

Genevieve

Nick Allgauer

Perfect, thank you!!

Genevieve P.

No problem! I'm glad that worked for you.

Bwoods113

@Genevieve P.

I am in need of the same formula I have looked at it for 15 min now and I cant figure out what I have wrong, would you mind looking to see if I have a syntax error in this?

=INDEX(COLLECT({Cert Import Temp Range 1}, {Cert Import Temp Range 2}, CONTAINS([Primary Column]@row)), 1)

Genevieve P.

Hi @Bwoods113

Happy to help 🙂

It's the CONTAINS function that's missing a piece. You need an @cell in there to tell it to check through each cell of the previously stated reference, like so:

=INDEX(COLLECT({Cert Import Temp Range 1}, {Cert Import Temp Range 2}, CONTAINS([Primary Column]@row, @cell)), 1)

That should do the trick!

Cheers,

Genevieve

Bwoods113

@Genevieve P.

Thank you so much I appreciate all your help.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.