Try this UDF:

Function findsubstr(substr)
findsubstr = True
On Error GoTo notfound
hitrow = Worksheets("Munka2").Columns("F:G").Find(What:=sub str,
After:=Worksheets("Munka2").Range("F1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Row 'Activate
Exit Function
notfound:
findsubstr = False
End Function


Replace Munka2 with the sheet name of company database. Add
workbooks("company database file name") if it's located in a separate
workbook!

Regards,
Stefi

€žCFR€� ezt Ã*rta:

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.