Formula that combines Contains and OR to search for values in one cell



I am new to smart sheet, and have the programming knowledge of a 1st grader :-)

I am attempting to write a formula that returns a specified value IF it finds ANY of the specified values in a cell (that's within the same row). I also want to know if the cell is NULL or if ANY of the values are NOT found.

For example:

Column 1, Row 1 cell contains the value: a b 4 8 2

Search in Column 1, Row 1, for any values that contain "4" or a or "2"

IF any one or more of these values are found, then:

Column 2, Row 1: = "Yellow"


IF any of these values are NOT found AND the Cell is Not NULL, return "GREEN",


IF the Cell is NULL, return "RED"

My Attempt to write this:

=IF(CONTAINS("a", Body1), OR (CONTAINS("D", Body1),"YELLOW", "GREEN")

How would I write this? I would appreciate any advice. Thank you so much.


Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!