Formula for matching addresses

[Guest]

Hi There,


Can someone help with a formula in excel to find matching addresses between
2 columns? I want to be able to find matching addresses despite the spacing,
upper/lower case formatting and punctuation used in the addresses. Is there a
formula that can help me pick out matching addresses from 2 lists? If there
is, please help me? Email me at (e-mail address removed) ..... Thanks,
Sean

 

[Bob Phillips]

Put this alongside the looked up value and copy down

=ISNUMBER(MATCH(TRIM(B2),TRIM($A$2:$A$20),0))

It is an arry formula, so commit with Ctrl-Shifte-Enter, not just Enter.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,


Thanks for the help. Either Im executing the formula incorrectly or it
doesnt work. I have two list lists of Postal Addresses. I need to extract the
ones that match but cant seem to find a proper formula as the Postal
Addresses arent in the same format. In other words, some are in Upper and
some lower case and some have 1 or more spaces inbetween and some have
punctuation eg in list 1 i have 23 Jones Lane and in list 2, I have 23
joneS Lane. This is a match but no formula will pick it out unless they are
identically spaced and the upper and lower case all match. Can you help? I
just need to pick out the common postal addresses in two lists so we can use
the information. Please help if u can? Thanks alot for the help. Cheers, Sean

 

[Bob Phillips]

Sean, the case is irrelevant in that formula, believe me.

Spaces are not so, but TRIM should manage that.

I put 23 Jones Lane in A9

I put 23 joneS Lane in B3

and this formula =ISNUMBER(MATCH(TRIM(B3),TRIM($A$2:$A$20),0)) returned
TRUE. I even added some extra spaces in it, no problem.

Remember, it is an array formula, so commit with Ctrl-Shift-Enter, not just
Enter.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)