Matching Addresses between two sets of data

**ceej561** · 02-20-2012, 08:45 PM

Hello,

I have two sets of addresses of unequal length. I am trying to see if those addresses in column A exist anywhere in Column E (Please see attached). How do I perform this function for each of the 195 addresses in Column A?

Thanks!

**rylo** · 02-20-2012, 09:33 PM

Hi

Highlight column E and go Data, Text to columns, and use a comma as the separator.

Then in B2 enter the formula =MATCH(TRIM(A2),E:E,0) and copy down. Where there is a match, you will find a number which is the row number of the address in column E.

HTH

rylo

**ceej561** · 02-20-2012, 09:41 PM

Perfect! That did exactly what I was looking for. If you feel like taking the time to explain why you had to use each of those syntaxes for MATCH, that would be great. Then I won't have to ask again next time I run into a slightly different version of this one...

**rylo** · 02-20-2012, 09:55 PM

Hi

If you look at the data in column A, you will find that there are a heap of trailing spaces. If you try to find the address with those extra spaces, then it won't match. By using trim, it gets rid of the extra spaces.

Then it is just a standard match function. The 0 at the end looks for an exact match, of the data from the first parameter (with the extra spaces removed) in column E.

rylo