Like match between 2 ranges

D

dean

Hi,
I'm trying to flag records that CONTAIN any of the strings in another
range. My purpose, in this specific case, is to examine a range of
website URLs and to remove any URLs belonging to particular domains.
For example:

ColumnA: List of URLS
www.sports.cnn.com/basketball.htm
www.aaa.baddomain.com/trouble.htm
www.groups.google.com/excel/
www.nothanksdomain.com
www.trouble.dontwantdomain.org/big/trouble/go/away/

ColumnB: List of domains to suppress
baddomain.com
nogooddomain.net
dontwantdomain.org
nothanksdomain.com

ColumnC: Result
I'm looking for a formulae that identifies if there is a LIKE match
between ColumnA and ColumnB and outputs TRUE/FALSE (or similar).

Thanks
Dean
 
B

Biff

Hi!

With the list of url's in column B starting in B1. Enter this formula in C1:

=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A$1:A$5))))

Copy down as needed. Any matches found will return a value greater than 0.
Zero means no matches found.

However, this in and of itself will not "flag" the url's in column A. You
could use conditional formatting to actually "mark" any matches.

Select the url's in column A.
Goto Format>Conditional Formatting
Formula is: =OR(ISNUMBER(SEARCH($B$1:$B$4,A1)))
Select a fill color or whatever style you would like.
OK out

Biff
 
D

dean

Hi,
THanks for that. I followed the instructions and all results are coming
up 0. Would you mind checking the formula?

Thanks for your help
Dean
 
D

dean

Hi,
searching for elements of the forumula you gave I found the following
on the web that is working for me:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$4,B1)))),"Found","Not Found")

Thanks for getting me there!
Dean
 
Top