Finding and returning values in a range of cells

edited 12/09/19 in Formulas and Functions

Hi all,

I'm having a lot of trouble joining cells that contain a certain value (because there is no contain function).  I have a range of cells that are all in a single row, and I'm trying to collect all of the cells that contain a certain value, and join them together.

For example, say I had a row with 4 columns that say "Cat","Umbrella Cat", "Dog","Frog", and I'm trying to isolate all of the columns that contain the word "Cat".  If this formula worked like I want it to, I would use a combination of Join and Collect functions to return "Cat, Umbrella Cat" in a separate cell.  This is as far as I've been able to get: JOIN(COLLECT([Row1]:[Row4],[Row1]:[Row4],="Cat"),", ").

That only gets me "Cat", not the other cell's value that contains the word Cat.

Does anyone have any suggestions on how to isolate and return cells that ~contain~ a certain value, not just equal it?  It would be great if Smartsheets could create a contain function and/or allow us to be able to use "FIND" across a range of cells.

Thank you!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!