Much better!
Stefi


€žRon Rosenfeld€� ezt Ã*rta:

On Mon, 3 Dec 2007 22:07:01 -0800, CFR wrote:

Excel 2000: Need to compare text in cells in sheets.

One spreadsheet has simplified company names as keywords in one column (e.g.
B1 = "xyz", B2 = "abc corp" etc.).
The other has a large company database, in which 2 columns contain company
names in non-standardized format (e.g. columns F and G contains long lists
like "xyz llp", "company xyz", "xyz.com", "abc corporation", "def unlimited"
etc.)

I need to check if the company names on my list are anywhere in the two
columns that have company names in the other spreadsheet. Ie. for each co
name - is the name in there or not?
Simply should return Yes in column C1 etc. if (text in simplified keyword
list B1) is contained in any (text in the 2 database company name columns);
No if nowhere in those 2 columns.

I have not found a MS formula that helps me get the text comparison right.
Your thoughts? Thanks.

Something like (not tested):

=IF((COUNTIF("*"&B1&"*",'Sheet2!'rng1)+COUNTIF("*" &B1&"*",'Sheet2!rng2)) =
0,"No","Yes")


--ron