Finding and returning values in a range of cells
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.