INDEX, COLLECT, CONTAINS

AMCGil

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)

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))))