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.
Answers


=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
Categories
Check out the Formula Handbook template!