Find a Match withing a Join?

How can I use MATCH to find if an instance occurs within a group of values joined with JOIN?

For example, if I join the values in three columns (Red Yellow Blue), no commas just a space, and I want to MATCH the value in a cell (say, "Red"), how can I see if the joined values (Red Yellow Blue) contains Red?

Right now, the result is no match, because the cell is not a perfect match.

I thought about finding a way to match the value in a cell (say, "Red") across three columns (rather than adding a helper column that joins the three values into a single column, but then I increase my reference cells. So, I though joining the values into a single cell would help.

Find more posts tagged with

Sheets

Formulas

Comments

Paul Newcome

Use the FIND function and state that if the result of that is greater than zero (meaning it exists somewhere within the string) to display "whatever you want". You can also use the JOIN function within the FIND function to eliminate that helper column if you wanted to.

=IF(FIND("Red", JOIN([Column1]@row:[Column3]@row, " ")) > 0, "whatever you want")

Paul Newcome

To use the MATCH function, you would eliminate the JOIN function altogether and just look across the range. This will also produce a numerical value, so wrapping it in an IF and stating to output "whatever you want" if the MATCH returns a value greater than zero.

=IF(MATCH("Red", [Column1]@row:[Column3]@row, 0) > 0, "whatever you want")

Kara Lumley

You can also use the new "Contains" without the joining or matching -

=IF(CONTAINS("Red", [Column 1]1: Column 3]1), "true", "false")

Andrée Starå

Hi Kara,

Great tip!

Thanks!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Definitely a good one. I haven't fiddled with the CONTAINS function much yet, so I had forgotten about it.

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…