Pulling Values from referenced sheet cell, where mutliple values exist in the cell.

Using the following formula to pull values from a source sheet, when it matches a cell in my current sheet.

=IFERROR(INDEX(COLLECT({eITCR # (Open)}, {eITCR - Open AI}, HAS([InfoCard Number]@row, @cell )), 1), INDEX(COLLECT({eITCR # (Closed)}, {eITCR - Closed AI}, HAS([InfoCard Number]@row, @cell )), 1))

The issue I have is that the referenced cell for {eITCR - Open AI} column, sometimes has more than 1 value in the cell separated by a comma. e.g. AI-2021-0123, AI-2021-0125

My formula returns the value if 1 value is in the cell, but pulls a #invalid value if not.

I've tried JOIN(COLLECT), which brings blank values rather than #invalid value and INDEX(MATCH) which brings back #no match.

Any suggestions?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Swap the HAS function for the CONTAINS function. Keep in mind the syntax is flipped when switching between the two.

All comments

Paul Newcome

Swap the HAS function for the CONTAINS function. Keep in mind the syntax is flipped when switching between the two.

Jack Parry

@Paul Newcome thanks! This way ending up working for me. Switching the @cell, didn't work.

=IFERROR(INDEX(COLLECT({eITCR # (Open)}, {eITCR - Open AI}, CONTAINS([InfoCard Number]@row, @cell )), 1), INDEX(COLLECT({eITCR # (Closed)}, {eITCR - Closed AI}, CONTAINS([InfoCard Number]@row, @cell )), 1))

Paul Newcome

Happy to 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.