LVExcel
Board Regular
- Joined
- Nov 23, 2011
- Messages
- 59
- Office Version
- 365
- Platform
- Windows
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.
<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
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