VLOOKUP Approximate Match Issue

LVExcel

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


A B C D E F
1Dest. ZIPGroundNext Day ZipCode Formula
2004-00500610610040 =VLOOKUP(LEFT(E2,3),A:C,2,TRUE)
3006-00704512511368
400904512520004
5010-04600610613005
604700710717796
7048-089006106
8100-129006106
9130-132005105
10133-139006106
11140-174005105
12175-176006106
13177-178005105
14179-199006106
15200-205005105
16206-208006106
17209-213005105

<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
 
Sorry, that clears up nothing...

Can you post another example, this time filling in your expected results to column F by hand.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sorry, that clears up nothing...

Can you post another example, this time filling in your expected results to column F by hand.


515-99

*ABCDEFGHIJ
9Dest. ZIPGround3 Day Select2nd Day Air2nd Day Air A.M.Next Day Air SaverNext Day Air*Enter ZipFormula
10004-005006306206246136106*00410#N/A
11006-007045-225--125*00510#N/A
12009045-225--125*11368006
13010-046006306206246136106*17515005
14047007307207247137107***
15048-089006306206246136106***
16100-129006306206246136106***
17130-132005305205245135105***
18133-139006306206246136106***
19140-174005305205245135105***
20175-176006306206246136106***
21177-178005305205245135105***

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:98px;"><col style="width:59px;"><col style="width:94px;"><col style="width:88px;"><col style="width:124px;"><col style="width:142px;"><col style="width:94px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
J10=VLOOKUP(LEFT(I10,3)&"*",A:G,2,1)
J11=VLOOKUP(LEFT(I11,3),A:G,2,1)
J12=VLOOKUP(LEFT(I12,3),A:G,2,1)
J13=VLOOKUP(LEFT(I13,3),A:G,2,1)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Okay, I followed your advice and installed Excel Jeanie. In the above spreadsheet I need to enter any Zip Code, even if a number between the ranges, for example the first Dest.Zip range is "004-005" (these ranges only show the left-most 3 digits of a zip code), if I entered 00410 (which is a 5 digit zip code), or 00468, or entered 00525, the formula should always return the data on the second column "006".

A FALSE VLOOKUP will only look at the left-most 3 digits and expect a perfect match, in the above example it will not find "005".
A TRUE VLOOKUP does not always get it correct, and it needs to be consistent since the formulas will be hidden and the user will expect information based on the Zip Code.

Let me know if you have any ideas.
 
Upvote 0
What are your 'Expected' Results based on that table?
Remove the formulas from Column J, and type by hand the results you expect to see..
And WHY are those your expected results?
 
Upvote 0
What are your 'Expected' Results based on that table?
Remove the formulas from Column J, and type by hand the results you expect to see..
And WHY are those your expected results?


515-99

*ABCDEFGHIJ
8*ZONES***
9Dest. ZIPGround3 Day Select2nd Day Air2nd Day Air A.M.Next Day Air SaverNext Day Air*Enter ZipFormula
10004-005006306206246136106*00410After user enters zip code in I10, depending on the service selected (ground, next day, etc.) in another cell not shown here, the combination of the two will return the zone number for that service. *Zones are shown under the service Columns B:G.
11006-007045-225--125*00510006 <---- this is he expected result for Ground
12009045-225--125*11368006 <---- this is he expected result for Ground
13010-046006306206246136106*17725005 <---- this is he expected result for Ground
14047007307207247137107***
15048-089006306206246136106***
16100-129006306206246136106***
17130-132005305205245135105***
18133-139006306206246136106***
19140-174005305205245135105***
20175-176006306206246136106***
21177-178005305205245135105***

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:98px;"><col style="width:59px;"><col style="width:94px;"><col style="width:88px;"><col style="width:124px;"><col style="width:142px;"><col style="width:94px;"><col style="width:64px;"><col style="width:64px;"><col style="width:317px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
If you can change the values in column A to have just the bottom end of each range, then a True lookup using the left/3 function will work


Excel Workbook
ABCDEFGHIJ
9Dest. ZIPGround3 Day Select2nd Day Air2nd Day Air A.M.Next Day Air SaverNext Day Air*Enter ZipFormula
100046306206246136106*004106
1100645-225--125*005106
1200945-225--125*113686
130106306206246136106*177255
140477307207247137107**
150486306206246136106**
161006306206246136106**
171305305205245135105**
181336306206246136106**
191405305205245135105**
201756306206246136106**
211775305205245135105**
Sheet2
 
Upvote 0
If a user is sending a GROUND shipment to zip 11368, based on that information, the spreadsheet will return the correct ZONE number which is needed to calculate the Rate (which is another calculation in my sheet).
 
Upvote 0
Thanks for your help, that's what I will have to do. At least this exercise has helped me to see the exact limitations of TRUE with VLOOKUP.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top