Nested IF/OR Contains Function



I'm trying to write a CONTAINS formula to auto-populate the project location column. There are 6 states to choose from. The Project Name is usually very long & descriptive but always contains the standard 2 letter code for the six states that the projects could be in. I came across the CONTAINS formula but cannot figure out how to write it so that it rolls through all of the options. Ex: If the Project Name column has a cell that contains "UT" it will spit out Utah in the Project Location column. If the Project Name has a WY in a cell it spits out Wyoming as the location etc. Each time I attempt to write the formula it gives me the Incorrect argument or the unparseable.

Thanks for any help!


Best Answer


  • Jennifer Greenough
    Jennifer Greenough Overachievers Alumni

    One alternative would be to create a column to split the state name from the project name (since it appears you use "-" as a delimiter"). See here for a handy guide on how to split text in Smartsheet. You could then use a VLOOKUP for the state using a secondary sheet. Training on VLOOKUP is available in the Learning Center here.

    This might be a bit cleaner in terms of data management and result in fewer mismatches.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!