Conditional formatting, search formula

Question

For reference, I have two data sets, one that with scraped names and one with manually input names. The Manual input set has broader coverage but the scraped set is more accurate.

So I have used textjoinif to create a list of the manually input towns for each company, I have the scraped towns each in its own cell in the same row. I want to have conditional formatting where each of the scraped values will be searched in the textjoin string associated with the same ID (same row), and highlighted green if there is a match.

It's entirely possible I'm making this more difficult than it needs to be, but this is being used for several thousand IDs and many thousand contacts

Here is the example I was using

                                                  Count   Towns
A01 Dunkin Donuts   Norwell          Dunkin Donuts  6   
A02 Honey Dew       Hanover          Honey Dew      3   
A01 Dunkin Donuts   Springfield      Beard Papa     2   
A03 Cronut          Walnut Creek     Cronut         2   
A04 Beard Papa      Culver City         
A03 Cronut          Santa Monica            
A01 Dunkin Donuts   Summerville         
A02 Honey Dew       Charlestown         
A04 Beard Papa      Oakland         
A01 Dunkin Donuts   Dorchester          
A01 Dunkin Donuts   Jamaica Plain           
A01 Dunkin Donuts   San Francisco           
A02 Honey Dew       Agoura          

Answer 1

highlight the entire range you want to apply the conditional formatting and ensure that the upper left hand corner of the selection set is the active cell.

go to conditional formatting and choose custom formula.

In the custom formula bar enter the following

Case sensitive
=FIND(G2,$f2)

NOT case sensitive
=SEARCH(G2,$f2)

apply your colouring and whatever other formatting you want and click ok/apply until you are back at your spreadsheet.

Answer 2

Conditional Formatting > New Rule > Use a formula to determine which cells to format > Use below formula > Apply to desired range

=COUNTIF($A$1,"*"&B1&"*")

Answer 3

Please select and clear any CF from ColumnsG:I and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(G1<>"",SEARCH(G1,$F1))

Format..., select highlighting of choice, OK, OK.