Vlookup only returning the last of duplicate values

[Hajimasabi]

I have a sheet with data similar to the following. (Sheet1)

........Col1 .................Col2
1 25145 Allure........ =VLOOKUP(A1,Sheet2!$A$1:$B$5,2)
2 25146 Allure
3 25147 Oak
4 25147 Pine
5 25147 Ash

The first column of the second sheet (Sheet2) contains the street address (common field) and other data I would like to merge over to the first using the Vlookup formula on the first page in column 3.

.........Col1 ...........Col2
1 25145 Allure ....POBox459
2 25146 Allure ....POBox125
3 25147 Oak ......POBox156
4 25147 Pine ......POBox784
5 25147 Ash ......POBox365

The formula works fine except where there are values that are partial duplicates such as 25147 Oak and 25147 Ash in which case the Vlookup returns the last (bottom) value for all of the rows like this:

........Col1 ..........Col2
1 25145 Allure ..... POBox 459
2 25146 Allure...... POBox 125
3 25147 Oak ........ POBox 365
4 25147 Pine ....... POBox365
5 25147 Ash .........POBox 365

Rows are formatted as text and are sorted A-Z and the common fields between sheets appear to be identical (no spaces or other weirdness). Can anybody help me figure out how to correct this issue? It appears that the formula is only looking at the first numerical part of each entry.
Many Thanks

 

[ChrisM]

Hello, you have omitted the last part of the vlookup formula. By doing so, you are telling it to find an exact or partial match. Change your formula to:

=VLOOKUP(A1,Sheet2!$A$1:$B$5,2,False)

If you don't enter this argument, it defaults to True, which is partial match, so always end your vlookups with False (or you can just put "0" without quotes, it's faster)

=VLOOKUP(A1,Sheet2!$A$1:$B$5,2,0)

 

[Hajimasabi]

I've tried adding the additional FALSE argument to the end. Unfortunately, this changes all results to #N/A.

 

[ChrisM]

Hmm, that says no match is found, which implies the spelling is slightly different, but you said you checked that already....

Sometimes Vlookups struggle with text vs. numbers. Try putting an apostrophe in front of two or three sample items. One that you use as the lookup, and the other in the lookup list. See if that works.

 

[Hajimasabi]

Problem Solved. One column contained an extra space between the numerical and text portions of the address. Used a find/replace to remove the extra spaces and voila! Thanks for jogging my brain.