Finding and returning values in a range of cells

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!

Find more posts tagged with

Formulas

Community Platform

Update Requests

Comments

Paul Newcome

Use a FIND function for the word Cat. I would even recommend throwing in a LOWER function to make sure you grab every cell containing the word regardless of upper or lower case.

JOIN(COLLECT([Row1]:[Row4], [Row1]:[Row4], FIND("cat", LOWER(@cell)) > 0),", ").

erober01

Hi Paul,

Thank you for this - I'm not sure if I'm missing something, but nothing comes up in the cell that I put this formula into. Would this formula search each cell? Or only the one cell I enter in the FIND formula?

Thank you,

Ellie

Paul Newcome

You would not specify a cell within the FIND function. The @cell reference indicates to check each cell within the range.

In taking another look, it seems you are looking across multiple columns along the same row. If this is correct, you are also going to need to adjust your ranges. I am not sure what your exact column names are, but it would end up looking something like this (leave the @row reference):

=JOIN(COLLECT([First Column Name]@row:[Last Column Name]@row, [First Column Name]@row:[Last Column Name]@row, FIND("cat", LOWER(@cell)) > 0),", ")

erober01

Hi Paul,

THANK YOU I never knew that @cell was a reference, not a way of saying [the cell you are looking for]. This makes everything so much easier! And it worked perfectly once I added @cell in.

Thank you so much for saving me hours of confusion

Best

Ellie

Paul Newcome

Happy to help!

And just for a little clarification... The @cell reference is more telling the formula to look at each cell within the range on an individual basis.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…