Multiple IF Statement Returning Different Text

Grete Willstrom

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.

heyjay

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.

Grete Willstrom

The above formula worked great. Now I am trying to do something where I create a value for each color then sum them together to get a total value. I am trying the same formula but it is not working. Here is what I am entering =IF(FIND("Black", Color@row) =1, 12, 0) +

 IF(FIND("Blue", Color@row) = 1, 5, 0) +

 IF(FIND("Brown", Color@row) = 1, 10, 0) +

 IF(FIND("Gold", Color@row) = 1, 2, 0) +

 IF(FIND("Green", Color@row) = 1, 8, 0) +

 IF(FIND("Red", Color@row) = 1, 9, 0) +

 IF(FIND("Yellow", Color@row) = 1, 15, 0)

I can get it to return the first value but after I am struggling. Please advise on what I am missing. Thanks for all the previous help.

Grete Willstrom

I actually just tried the AI generator with the formula. The change that needs to be made is to update "find" to "contains" then it works.

 IF(CONTAINS(("Blue", Color@row) = 1, 5, 0) +

 IF(CONTAINS(("Brown", Color@row) = 1, 10, 0) +

 IF(CONTAINS(("Gold", Color@row) = 1, 2, 0) +

 IF(CONTAINS(("Green", Color@row) = 1, 8, 0) +

 IF(CONTAINS(("Red", Color@row) = 1, 9, 0) +

 IF(CONTAINS(("Yellow", Color@row) = 1, 15, 0)