# VLOOKUP problem with zip codes

#### tati

##### New Member
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.

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
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
They are both formatted as Special-->Zip. Where are you finding text in the zip codes?

#### jbeaucaire

##### Well-known Member
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

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
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

Thanks!

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

#### texasalynn

##### Well-known Member
you could create a formula
=istext(a1)
copy that down

#### jbeaucaire

##### Well-known Member
Real numbers format themselves to the right-hand side of a cell by default, and text defaults to the left.

#### jbeaucaire

##### Well-known Member
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.

Replies
0
Views
136
Replies
1
Views
710
Replies
24
Views
720
Replies
37
Views
2K
Replies
1
Views
317

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.

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.

### Which adblocker are you using?

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

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