Formula for return X value if there is ANY text in a cell, otherwise return text in another cell

Hi! I am working on a formula for a sheet that has a product column, a Use Cases column and a Tool column. I'm hoping to use a formula that says, "Look in the Use Cases column for any text. If you find text, return a "Use Case" in the Product column. If you can't find any words, then return the value in the Tool column in the Product column.

I was using the =IF(CONTAINS("a", [Use Cases]@row), "Use Case") formula.. but it's not quit there for two reasons:

  1. it the Use Cases column doesn't have a letter "a" then it will error out (row 7 as an example)
  2. I don't know how to write an exemption to the formula to return the "Tool" if the Use Cases column is blank.

In the image below, Row one of the sample, I would want it to return "Tool A" and using the current formula, row 7 won't return anything because it doesn't have an "a" in the Use Cases column.

What is the correct formula?

Thank you for the help!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!