Prev: NONFUNCTIONING FORMULA
Next: references rename!
From: Paige Wolfgram Paige on 2 Apr 2010 16:31
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
From: David Billigmeier on 2 Apr 2010 16:44
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)))

--
Regards,
Dave


"Paige Wolfgram" wrote:

> 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
From: Pete_UK on 2 Apr 2010 16:46
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

On Apr 2, 9:31 pm, Paige Wolfgram <Paige
Wolfg...(a)discussions.microsoft.com> wrote:
> 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

 | 
Pages: 1
Prev: NONFUNCTIONING FORMULA
Next: references rename!