VLOOKUP Approximate Match Issue

[LVExcel]

I need help with using VLOOKUP approximate match (Excel 2010).

My formula result is either #N/A or the previous row is being returned; in some instances the correct zone is returned.
My desired result is to lookup a ZipCode (example: 13015, only first 3 digits matter), and return the Zone for the corresponding shipping Service (Ground or NextDay). For "10040" it should return the zone in cell B8.

I'm trying to avoid having to break out the list, for example: "004-005" equals, 00401, 00402, 00403 ... to 00599. It's a long list.

	A	B	C	D	E	F
1	Dest. ZIP	Ground	Next Day		ZipCode	Formula
2	004-005	006	106		10040	=VLOOKUP(LEFT(E2,3),A:C,2,TRUE)
3	006-007	045	125		11368
4	009	045	125		20004
5	010-046	006	106		13005
6	047	007	107		17796
7	048-089	006	106
8	100-129	006	106
9	130-132	005	105
10	133-139	006	106
11	140-174	005	105
12	175-176	006	106
13	177-178	005	105
14	179-199	006	106
15	200-205	005	105
16	206-208	006	106
17	209-213	005	105

<tbody>
</tbody>

To see data I'm dealing with, you can quickly download the list at:

UPS: Zones and Rates for the 48 Contiguous States

 

[hodpy62]

I think you may need to add in another column like this:

Short Zip	Dest. ZIP	Ground	Next Day	ZipCode	Formula
004	004-005	6	106	10040	6
006	006-007	45	125	11368	6
009	009	45	125	20004	5
010	010-046	6	106	13005	5
047	047	7	107	17796	5
048	048-089	6	106
100	100-129	6	106
130	130-132	5	105
133	133-139	6	106
140	140-174	5	105
175	175-176	6	106
177	177-178	5	105
179	179-199	6	106
200	200-205	5	105
206	206-208	6	106
209	209-213	5	105
=left(C19,3)

<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[hodpy62]

I think you may need to add in another column like this:

Short Zip	Dest. ZIP	Ground	Next Day	ZipCode	Formula
004	004-005	6	106	10040	6
006	006-007	45	125	11368	6
009	009	45	125	20004	5
010	010-046	6	106	13005	5
047	047	7	107	17796	5
048	048-089	6	106
100	100-129	6	106
130	130-132	5	105
133	133-139	6	106
140	140-174	5	105
175	175-176	6	106
177	177-178	5	105
179	179-199	6	106
200	200-205	5	105
206	206-208	6	106
209	209-213	5	105
=left(C19,3)

<tbody>
</tbody>

then get it to look up the short zip rather than the full one ..

 

[Jonmo1]

Perhaps

=VLOOKUP(LEFT(E2,3)&"*",A:C,2,FALSE)

 

[hodpy62]

Perhaps

=VLOOKUP(LEFT(E2,3)&"*",A:C,2,FALSE)

Yeah thats a better way to do it, it works and is a but simpler nice one

 

[LVExcel]

Perhaps

=VLOOKUP(LEFT(E2,3)&"*",A:C,2,FALSE)

Hey, that worked!!! Can you share the logic behind this. Why is this needed when I'm only looking at the first 3 left digits.

 

[Jonmo1]

The "approximate match" function of vlookup is really intended for use on Numerical Ranges.
1
10
20
30
=VLOOKUP(12,A:B,1,TRUE) = 10

It doesn't work very well with TEXT data, and you get very strange results. As you've seen.
When dealing with TEXT, exact match is really best.


Now the Exact Match does allow wildcards, that's what the &"*" does
If E2 = 10040 then LEFT(E2,3)&"*" = 100*
The * is the wildcard, so it only looks at the first 3 characters in Column A.

 

[LVExcel]

Perhaps

=VLOOKUP(LEFT(E2,3)&"*",A:C,2,FALSE)

Unfortunately this will not work in every instance. The formula with FALSE is looking for an exact match of the first 3 digits on the LEFT regardless of what follows; however, what these numbers represent are ranges of Zip Codes. For example: "004-005" equals, 00401, 00402, 00403 ... to 00599. When looking for "005" I get #N/A since those are not the LEFT most 3 digits.

Hodpy62's suggestion will work, however, other users will be using this file and have no idea how to enter formulas; I prefer to use a formula that I can keep hidden.

Any ideas? I like the direction Jonmo1 is taking, but its not there yet.

 

[Jonmo1]

I'm confused...

You said.

My desired result is to lookup a ZipCode (example: 13015, only first 3 digits matter), and return the Zone for the corresponding shipping Service (Ground or NextDay). For "10040" it should return the zone in cell B8.

Now you DO want to look at the right digits....

 

[LVExcel]

I'm confused...

You said.


Now you DO want to look at the right digits....

No. That's my fault, I mean the first 3 digits on the far LEFT.