Heather has Sales Rep assignments stored next to a range of Zip Codes. For example, John is responsible for the territory that includes zip codes 41000-44699. "How can I use a VLOOKUP that pulls Data that is stored in Ranges like this?" Today, in Episode #1676, Bill shows us how to get the Zip Code and offers a few tips on Data Setup as well.
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1676: VLOOKUP into a range of zip codes.
Oh boy, today's question is sent in by Heather.
Heather wants to know if I could maybe knock out some VBA.
She has zip codes, postal codes for those of you not in the United States where they show the range right, so Barry gets all postal codes 04948-07371 and here we have some zip codes and we need to figure out who the Rep is.
So for 22820, well that would be Ollie.
We don't want to have to like do this manually and type them all in, we want to do a VLOOKUP or something like that.
All right, so this is pretty wild.
I want to go look for this zip code value in a range of just the left most five characters of column A.
So what we're going to do is we want to say =MATCH(D3,1+LEFT($A$2:$A$44,5).
For this range I will press F4 to lock that down comma 5 so what we're going to get is 00001 as text but when I add 1 to that it's going to convert it to a number.
Oh wait this should be 0+ instead so =MATCH(D3,0+LEFT($A$2:$A$44,5).
All right, so that way we get just the left most characters and what that's going to give us provided that this is sorted over here is the starting zip code for Larry and then the starting zip code for Debbie.
The starting zip code for Debbie, now that's funny.
Why did they do that?
Who knows.
Okay and then what we want, we want the value just less than which is the same as doing “True” in VLOOKUP.
Now, because this is returning a range and array of values we have to press Ctrl+Shift+Enter here.
I actually held my breath when I did that the first time and sure enough it's returning the eleventh row in the range.
That's beautiful.
Now all we have to do is use the INDEX function, of these names here in B2 to B44, press F4 to lock that down comma which road we want it's the answer from the match and we don't need to specify which column since it's a single column.
So the formula is =INDEX($B$2:$B$44,MATCHD3,0+LEFT($A$2:$A$44,5) then Control+Shift+Enter gets us Ollie.
Double-click to shoot that down.
All right now you'll see here the zip code up in New England is stored without the leading 0.
The proper thing to do is to store these as numbers and then use the custom Number format, actually there's a special number format in the US for zip code which is really a custom number format of five zeros click OK and click OK.
Well that'll be great but sometimes people will store these numbers as text and that's going to cause a problem over here.
If you have numbers stored as text you're going to want to convert them back to numbers just you know equal the text + 0 or x 1 to convert those back to regular numbers and use that inside of the match.
So instead of D3 you'll do D3+0 if these are actually stored as text.
Now all of this Heather, assumes that the people have actually used five-digit zip codes and they haven't shortened it like 073 through 078.
If they've done that it's going to become much harder.
All right, well, hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1676: VLOOKUP into a range of zip codes.
Oh boy, today's question is sent in by Heather.
Heather wants to know if I could maybe knock out some VBA.
She has zip codes, postal codes for those of you not in the United States where they show the range right, so Barry gets all postal codes 04948-07371 and here we have some zip codes and we need to figure out who the Rep is.
So for 22820, well that would be Ollie.
We don't want to have to like do this manually and type them all in, we want to do a VLOOKUP or something like that.
All right, so this is pretty wild.
I want to go look for this zip code value in a range of just the left most five characters of column A.
So what we're going to do is we want to say =MATCH(D3,1+LEFT($A$2:$A$44,5).
For this range I will press F4 to lock that down comma 5 so what we're going to get is 00001 as text but when I add 1 to that it's going to convert it to a number.
Oh wait this should be 0+ instead so =MATCH(D3,0+LEFT($A$2:$A$44,5).
All right, so that way we get just the left most characters and what that's going to give us provided that this is sorted over here is the starting zip code for Larry and then the starting zip code for Debbie.
The starting zip code for Debbie, now that's funny.
Why did they do that?
Who knows.
Okay and then what we want, we want the value just less than which is the same as doing “True” in VLOOKUP.
Now, because this is returning a range and array of values we have to press Ctrl+Shift+Enter here.
I actually held my breath when I did that the first time and sure enough it's returning the eleventh row in the range.
That's beautiful.
Now all we have to do is use the INDEX function, of these names here in B2 to B44, press F4 to lock that down comma which road we want it's the answer from the match and we don't need to specify which column since it's a single column.
So the formula is =INDEX($B$2:$B$44,MATCHD3,0+LEFT($A$2:$A$44,5) then Control+Shift+Enter gets us Ollie.
Double-click to shoot that down.
All right now you'll see here the zip code up in New England is stored without the leading 0.
The proper thing to do is to store these as numbers and then use the custom Number format, actually there's a special number format in the US for zip code which is really a custom number format of five zeros click OK and click OK.
Well that'll be great but sometimes people will store these numbers as text and that's going to cause a problem over here.
If you have numbers stored as text you're going to want to convert them back to numbers just you know equal the text + 0 or x 1 to convert those back to regular numbers and use that inside of the match.
So instead of D3 you'll do D3+0 if these are actually stored as text.
Now all of this Heather, assumes that the people have actually used five-digit zip codes and they haven't shortened it like 073 through 078.
If they've done that it's going to become much harder.
All right, well, hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
