Formula that combines Contains and OR to search for values in one cell
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.
Best Answers
-
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"))
-
Thank you so much!!
Answers
-
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"))
-
Thank you so much!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!