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:

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

Forum statistics

Threads
1,081,560
Messages
5,359,608
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top