INDEX, COLLECT, CONTAINS

I'm trying to pull a specific number out of another SmartSheet based on it matching information in one specific cell and getting #INCORRECT ARGUMENT SET.

The criteria is Doc Type being somewhere in the cell of PDs.

PDs@row - The column that contains a string of text like "SOP-12345"

Adherence Days- The number to bring back

Doc Type - Can contain things like "SOP" or "WRK"

=INDEX(COLLECT({Adherence Days}, PDs@row, CONTAINS({Doc Type}, @cell)), 1)

Find more posts tagged with

Accepted answers

All comments

Gillian C

Assuming that your PDs have a similar format i.e. SOP-XXXX, BED-XXXX etc I would put in a helper column to identify the document type, then use an INDEX Match formula

So create the helper column (for this example I've called it [Document Type]), the column formula within this helper column will then be

=LEFT([PDs]@row, FIND("-",[PDs]@row)-1)

The formula for [Adherence Days Return] then becomes

= INDEX({Adherence Days}, (MATCH([Document Type]@row, {Doc Type},0)))

Where {Adherence Days} and {Doc Type} are linked to the other sheet with the range of [Adherence Days] and [Doc Type] respectively.

Hope that helps?

Gillian C

Also if you didn't want to have the helper column you could use

= INDEX({Adherence Days},(MATCH((LEFT([PDs]@row,FIND("-",[PDs]@row)-1)),{Doc Type},0))))

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.