Vlookup only returning the last of duplicate values

Hajimasabi

New Member
Joined
May 3, 2011
Messages
3
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

New Member
Joined
May 3, 2011
Messages
3
I've tried adding the additional FALSE argument to the end. Unfortunately, this changes all results to #N/A.
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

New Member
Joined
May 3, 2011
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,106
Messages
5,622,766
Members
415,926
Latest member
jerrynababa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top