Thanks, Ron and Stefi.

Ron's suggestion ALMOST works - except that COUNTIF expects arguments
COUNTIF(range,criteria).

Keeping it at one range only to simplify, that would make it
=IF(COUNTIF('Sheet2!'rng1,"*"&B1&"*"...???) = 0,"No","Yes")

Can you please help with the ...???
TIA!

CFR


"Ron Rosenfeld" wrote:

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