Need Help with Index Collect Formula

Hello,

The formula i am trying to put together is below:

=INDEX(COLLECT({HBC Master File Product}, {HBC Master File Formula}, HAS(@cell, [HBC #]@row), {HBC Master File Plant}, CONTAINS(@row, "CBG COLD SPRING - G")), 1)

I want to search another sheet (HBC Master File Product) in the formula column to match the HBC#, the second criteria is to search the HBC Master file Plant column if it contains a key word. When i put in the first part, the formula works, it is when I am putting in the key word to look for that it says #unparseable.

Thank you,

Find more posts tagged with

Formulas

Accepted answers

All comments

DKazatsky2

Hi @tchav,

See if it works if you change "@row" to "@cell" in the CONTAINS function.

Hope this helps,

Dave

tchav

Now it gives #INVALID VALUE

Genevieve P.

Hi @tchav

Invalid Value will appear when the formula can't find a match for your two criteria. Can you test putting a filter on your source sheet with the two details:

The content in [HBC #]@row
"CBG COLD SPRING - G"

Keep in mind it needs an exact match, so if any of your characters are out of place it won't find the relevant row.

You can also test this by using COUNTIFS to see how many rows it finds in your source sheet:

=COUNTIFS({HBC Master File Formula}, HAS(@cell, [HBC #]@row), {HBC Master File Plant}, CONTAINS(@cell, "CBG COLD SPRING - G"))

If this says 0, it's not finding any matches.

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.