Lookup Issue - Criteria Word Variation

[artinj]

Hi everyone,

I am trying to find a solution to a vlookup issue. I have attached to this post a sample excel file which will make it easier to understand.
I have data in one table (Table 1) where the records consist of addresses with street field, city and zip. These records have to be flagged based on the street address (number and street name provided the city or zip is the same) with a flag called "Type"(as an example Type 1 or type 2, etc).
Than I have another table (Table 2 or the lookup table) that has unique adrresses with the "Type" field that needs to be brought over to Table 1.
The issue that I run into is that addresses in Table 1 have variations such as 25 Main Street can be as 25 Main St., or 25 Main St, or 25 Main st., Apt 2, which all should be recognized as 25 Main Street. All we care is to flag 25 Main Street with it's appropriate flag from Table 2.
Data in Table 1 can be in the 100k or more records in some cases so any manual intervention would be very time consuming and not efficient.
Now I've tried doing lookup only on the 3 or 4 left characters of this field but than you get thrown a curveball when the address is for example 25 Maitland Avenue, which will falsly flag them as 25 Main Street.
Plus house numbers can be 25 Main, or 255 main 2554 Main or 2 Main, so it is not functional to use the left function nested within vlookup.
This issue seems to be more of a logical validation, which I don't know how to approach.
The best way to think about this is when you go print a priority label on the USPS website and they correctly validate your address eventhough you might have mistyped your address.

Thank you in advance for your help. Any comments or suggestions will be greatly appreciated.

[orlandom]

Hi artinj,

To solve your problem you can use the following formula:

=VLOOKUP(LEFT(A5,FIND(" ",A5,4))&"*",$G$4:$J$6,4,FALSE)

As you may notice we are just interested in the street and number, the rest of the address is not important. The street and number are located at the left of the second space " ", so what I did was extract them with this part of the formula LEFT(A5,FIND(" ",A5,4)).

The asterisk wildcard character(*) represents any number of characters to the right of the street and number.

I have attached the solution to this post.

I hope this helps!

Orlando Mezquita

[artinj]

orlandom,
That formula works wonders. It brought down the amount of manual work considerably and made the population more managable. Thanks for your help and input.

Regards,

Arti