Normalize and Match Addresses

[richmcgill]

How would I create a scenario where I can match the addresses when they are the same but have different street types and some zip codes are five digits and some are five plus four?

These are borrower addresses and they appear in many variations in a couple of places in our system and we are trying to match them so we can identify that we have the right address just in a different format.

Below are a few examples.
If the information is the same but in a different format I would want to see a "Match with an Exception".
If the data matches exactly it would be a "Match".
If there is not a match anywhere then it would be a "No Match".

Is there a formula that would help us compare the addresses? Sometimes these are side by side in column A and B and sometimes we are searching an entire spreadsheet for a match. Is there a way to say which row the match is on if they are not side by side in columns A & B?

Address	Address
12345 Main St Jonesville CA 12345	12345 Main Street Jonesville CA 12345
7894 Ash Drive Smithville CO 12378	7894 Ash Dr Smithville CO 12378
12 Armstrong BLVD Frankville TN 12345123	12 Armstrong BLVD Frankville Tennesee 12345123
111 Unit 12 Garyville Fl 12385	111 Number 12 Garyville FL 12385
1256 #1777 Hopeville GA 12396	1256 # 1777 Hopeville GA 12396
8555 # 9865 Larryville TX 12374	8555 Unit9865 Larryville TX 12352