VLOOKUP problem with zip codes

[tati]

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!

 

[texasalynn]

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]

They are both formatted as Special-->Zip. Where are you finding text in the zip codes?

 

[jbeaucaire]

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]

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]

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.

 

[tati]

Thanks!

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

 

[texasalynn]

you could create a formula
=istext(a1)
copy that down

 

[jbeaucaire]

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

 

[jbeaucaire]

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.