Multiple IF Statement Returning Different Text

Hi Everyone,

I am trying to write a formula that will look at a cell and if certain wording is not in the cell then return a value to a new cell. The information is from a multiple dropdown selection column. Here is an example of what i am trying to do.

Dropdown List


Would Populate the Missing Color column based on a formula so it would end up looking like this in the sheet


The formula I am trying will populate the first missing color but I can't figure out how to populate all the missing colors. Here is what I have for the formula:

=IF(OR(NOT(CONTAINS("Blue,", Color@row))), "Blue, ", IF(OR(NOT(CONTAINS("Green,", Color@row))), "Green, ", IF(OR(NOT(CONTAINS("Red,", Color@row))), "Red, ")) ETC.


Any help would be greatly appreciated.

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Grete Willstrom

    Nested IF statements will stop as soon as they find a correct statement. Instead, you'll want to add individual IF statements together when you look for each individual value.

    Try something like:

    =IF(OR(NOT(CONTAINS("Blue,", Color@row))), "Blue, ") + IF(OR(NOT(CONTAINS("Green,", Color@row))), "Green, ") + IF(... etc

    Let me know if that makes sense and works for you.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!