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, ", IF(OR(NOT(CONTAINS("Yellow,", Color@row))), "Yellow, ", IF(OR(NOT(CONTAINS("Black,", Color@row))), "Black, "))))) ETC


Any help would be greatly appreciated.

Tags:

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    edited 01/26/24
  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    =IF(FIND("Black", Color@row) = 0, "Black, ", "") +
     IF(FIND("Blue", Color@row) = 0, "Blue, ", "") +
     IF(FIND("Brown", Color@row) = 0, "Brown, ", "") +
     IF(FIND("Gold", Color@row) = 0, "Gold, ", "") +
     IF(FIND("Green", Color@row) = 0, "Green, ", "") +
     IF(FIND("Red", Color@row) = 0, "Red, ", "") +
     IF(FIND("Yellow", Color@row) = 0, "Yellow", "")
    

    ...and as a single line...

    =IF(FIND("Black", Color@row) = 0, "Black, ", "") + IF(FIND("Blue", Color@row) = 0, "Blue, ", "") + IF(FIND("Brown", Color@row) = 0, "Brown, ", "") + IF(FIND("Gold", Color@row) = 0, "Gold, ", "") + IF(FIND("Green", Color@row) = 0, "Green, ", "") + IF(FIND("Red", Color@row) = 0, "Red, ", "") + IF(FIND("Yellow", Color@row) = 0, "Yellow", "")
    

    Hi, @Grete Willstrom , this will work. Use FIND() to check if the text (color) exists. Add it to the string if FIND()=0. Do this for each "color".

    If you don't what to have the hanging ", " like in the first row below, use the IF() statement below to remove it.

    IF( RIGHT(xx, 2) = ", " , LEFT(xx, LEN(xx)-2) , xx where xx is the formula above.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!