IF Formula

Hi everyone, I have two columns. One is Department, the other is Analyst.

I have created an if statement under the analyst column saying - =IF([Lead Department]2 = "Education", "Analyst Name". The formula is a lot longer because I added 13 departments in total, but it works.

That said, in some cases we need to select multiple departments under the department column. Ex - Central Services and Municipal Relations. It would appear as follows:

Is there a formula I can create that can identify which department/s are selected an than list the correct names under the Analyst column?



  • David Tutwiler
    David Tutwiler Overachievers Alumni

    I think you're looking for CONTAINS. So

    =IF(CONTAINS("Education", [Lead Department]2), "Analyst Name"

  • Lila De Vera
    Lila De Vera ✭✭✭✭✭

    Hi @David Tutwiler, i'm getting an UNPARSEABLE error,

    What i'm trying to do is list multiple analysts if the department column has multiple departments selected.

    Can an IF(CONTAIN( statement be nested, similar to IF statements?

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    I think you're getting the Unparseable error because you are giving too many parameters to the CONTAINS formula. CONTAINS only takes 2 parameters (#1 what are you searching for, #2 where to search). I think maybe the 'Name1' should be on the outside of the CONTAINS as the "what do I do if the IF solves true" part of the formula. Once that's done I think it should work fine.

    Yes, they can be nested as the CONTAINS is just nested within the IF statement).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!