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

Options

Howdy!

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"

OR

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

OR

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.

Tags:

• ✭✭✭✭✭
Options

If your row has no other values, the RED will not show up. Smaartsheet needs to see values in other cells, or the formula won't return its result.

=IF(LEN([Column1]@row) < 1, "RED", IF(OR(CONTAINS("4", [Column1]@row), CONTAINS("2", [Column1]@row)) = true, "yellow", "green"))

Options

Thank you so much!!

• ✭✭✭✭✭
Options

If your row has no other values, the RED will not show up. Smaartsheet needs to see values in other cells, or the formula won't return its result.

=IF(LEN([Column1]@row) < 1, "RED", IF(OR(CONTAINS("4", [Column1]@row), CONTAINS("2", [Column1]@row)) = true, "yellow", "green"))