Compare text in 2 different files

[Paige Wolfgram]

I am using Excel 2003 and have two files containing mailing information. I
need to check and see which companies are on both lists. I can't use a
VLOOKUP formula because some of the company names are slightly different.
e.g. One may have a LLC or Inc. at the end and the other doesn't. Is there a
formula I can use to compare the first 4 or 5 letters in the company name
between the two files? Thanks! Paige

 

[David Billigmeier]

Assume you're 2 lists are in A1:A10 and B1:B10 (change to fit your data).
Also change the 4 in the LEFT() function to be the number of characters you
want to compare. Commit this with CTRL+SHIFT+ENTER, as it's an array
function:

=SUM(--ISNUMBER(MATCH(LEFT(A1:A10,4),LEFT(B1:B10,4),0)))

 

[Pete_UK]

You can use wildcard characters with VLOOKUP, so if you want to match
on the first 5 characters in cell A2, for example, you could have
something like this:

=VLOOKUP(LEFT(A2,5)&"*",table,col,0)

where table and col should be obvious to you.

Hope this helps.

Pete