INDEX, COLLECT, CONTAINS

Options
AMCGil
AMCGil ✭✭✭
edited 04/05/24 in Formulas and Functions

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)



Answers

  • Gillian C
    Gillian C ✭✭✭✭✭
    Options

    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
    Gillian C ✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!