VLOOKUP problem with zip codes

tati

New Member
Joined
Feb 10, 2009
Messages
5
VLOOKUP returns incorrect results when comparing zip codes across worksheets. I think the problem could be caused by 2 things:

1. There are duplicate rows in the lookup_value range because each represents an individual and needs to remain a duplicate to show whether there are multiple individuals from the same zip. In these cases, VLOOKUP only returns the first matching zip from rows containing duplicate zips. (See 07042 in worksheet 1 of the file linked below.)

2. Zip code format is not being treated as a number during sort. Do I need to use a different format during sort? Don't want to lose the leading zeros on the zips.

File is posted here: http://www.yousendit.com/download/U0d3UGhUSEIwMEZMWEE9PQ

How can I make VLOOKUP work correctly in this context? Thanks!
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
your zip on sheet "2" are not the same format, they are a mix of text and numbers

you need to make all the numbers in the same format. If they were all of one kind you could change the formula to

=VLOOKUP(A1*1,'2'!$A$1:$B$4884,2,FALSE)
 

tati

New Member
Joined
Feb 10, 2009
Messages
5
They are both formatted as Special-->Zip. Where are you finding text in the zip codes?
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
VLOOKUP and INDEX(MATCH) (I would use INDEX(MATCH) for your exercise, BTW), both work fine. You're suffering from data that looks one way but is something else.

Zip codes beginning in 0 won't stay in the cell unless entered as text. You used a Custom Number Format to add back in the leading zeros, that adds them in VISUALLY, not realistically.

You will need to have the data on both sheets identical, most like entered as text with '00501 or '07042. If both lists on both sheets are setup properly, the matching works fine.

Having said that, you're going to have some trouble getting matches to multiple entries of the same zip code with different areas. It's not impossible, but it's not going to happen with a single formula. With both VLOOKUP and INDEX/MATCH you get to flag to receive the first match found (FALSE) or the last match found (TRUE), but the TRUE flag will also return fuzzy matches if the code searched isn't there at all, too.
 

tati

New Member
Joined
Feb 10, 2009
Messages
5

ADVERTISEMENT

Ah, didn't know that about zip formatting.

Could you show me a sample INDEX formula using the linked file? I find the Excel formula wizard hard to use for VLOOKUP and INDEX.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
To replace a VLOOKUP:

=INDEX(RangetoReturnValuesFrom,MATCH(CellToMatch,RangeToMatchFrom,0)

In your sheet it would look like this in Sheet1 B1

=INDEX('2'!$B$1:$B$4884,MATCH(A1,'2'!$A$1:$A$4884,0))

The last parameter 0 is the same as FALSE, which means "exact match". A 1 or TRUE would allow fuzzy matches and if the value isn't found, shows the next matching value below. For FALSE, the data doesn't need to be sorted, for TRUE it must be sorted.

INDEX/MATCH/MATCH is the most useful since it will look up a value in a rectangular table. The first match let's you spot the correct row, the second match spots the right column, giving you an X,Y coordinate within the table and showing you the value in that position.

Cool stuff.
 
Last edited:

tati

New Member
Joined
Feb 10, 2009
Messages
5

ADVERTISEMENT

Thanks!

One last question: How do I see which zips are stored as text and which as numbers?
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Real numbers format themselves to the right-hand side of a cell by default, and text defaults to the left.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Another benefit of INDEX/MATCH over VLOOKUP is that VLOOKUP only searches the left column in the range and returns values from columns to the right. INDEX/MATCH let's you search any range for a value and bring back a corresponding value from another range on either side of the range, left or right.

HLOOKUP only returns values on or below the searched row, INDEX/MATCH will return values above or below.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,193
Messages
5,768,766
Members
425,492
Latest member
blueexcel123

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