Find a Match withing a Join?

Art Schneiderheinze

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.

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.