Can I use multiple contains in a formula?

Travis Griffin

I'm trying to get a status column to display a color, based on a "named status" cell contents. For example, if @cell contains "Completed", "Green".

But I have 4 conditions, and I have 4 colors. 1 for each condition. And I'm badly missing something here. I was able to get it to change for the first statement, but anything after, it blows up:

=IF(CONTAINS("In Progress", [Named Status]:1), "Blue", IF(CONTAINS("Pending 3rd Party", [Named Status]1), "Yellow", IF(CONTAINS("Completed", [Named Status]1, "Green"))))

Paul Newcome

WHy exactly are you using the CONTAINS function? If you are only going to have one or the other you can drop the CONTAINS and just use:

=IF([Named Status]1 = "In Progress", "Blue", IF([Named Status]1 = "Pending 3rd Party", "Yellow", IF([Named Status]1 = "Completed", "Green")))

If you must use the CONTAINS function it would look more like this:

=IF(CONTAINS("In Progress", [Named Status]1), "Blue", IF(CONTAINS("Pending 3rd Party", [Named Status]1), "Yellow", IF(CONTAINS("Completed", [Named Status]1), "Green")))

Paul Newcome

Are you getting an error message or incorrect results?

Travis Griffin

Unparseable

Paul Newcome

WHy exactly are you using the CONTAINS function? If you are only going to have one or the other you can drop the CONTAINS and just use:

=IF([Named Status]1 = "In Progress", "Blue", IF([Named Status]1 = "Pending 3rd Party", "Yellow", IF([Named Status]1 = "Completed", "Green")))

If you must use the CONTAINS function it would look more like this:

=IF(CONTAINS("In Progress", [Named Status]1), "Blue", IF(CONTAINS("Pending 3rd Party", [Named Status]1), "Yellow", IF(CONTAINS("Completed", [Named Status]1), "Green")))

Travis Griffin

Thanks. The "Contains" did the trick. The "no contains" still not working, but I'm good with the other way.

Paul Newcome

Happy to help. 👍️