FedEx shipping rates

[gimage]

How would I calculate shipping rates for ground & Express Saver based on the first 3 digits of the zip code and the weight? FedEx has the rates and zones in Excel which I have combined into 3 sheets, zone, ground and esp.
I'd like to enter a 3 digit zip code and the weight with the result showing both ground & Express Saver rates. Some of the zip codes have a range, 010-046 for zone 5 for example. Some do not have a zone which may mean that zip code does not exist.

 

[gimage]

       ---A---- --B--- --C--- --D--- --E--- --F--- ---G--- ---H--- ---I--- J ---K---- -L--
   1   Dest Zip  76203                                                       Dest Zip Zone
   2   Wgt           3                                                              0 #N/A
   3   Cost     $6.73                                                               5    5
   4                                                                               10    5
   5                                                                               47    6
   6   Wgt      Zone 2 Zone 3 Zone 4 Zone 5 Zone 6 Zone 7  Zone 8  Zone 9          48    5
   7          1 $5.17  $5.40  $5.51  $5.75  $6.04  $6.12   $6.22   $19.18         130    4
   8          2 $5.37  $5.72  $6.22  $6.34  $6.75  $6.89   $7.13   $21.30         133    5
   9          3 $5.45  $5.97  $6.53  $6.73  $7.15  $7.36   $7.88   $23.21         140    4
  10          4 $5.58  $6.16  $6.86  $7.17  $7.53  $7.85   $8.44   $25.37         180    5
  11          5 $5.79  $6.25  $7.15  $7.46  $7.83  $8.20   $8.91   $27.63         200    4
  12          6 $5.96  $6.44  $7.27  $7.65  $7.97  $8.44   $9.10   $30.01         213 #N/A
  13          7 $6.26  $6.63  $7.41  $7.85  $8.20  $8.66   $9.40   $32.06         214    5
  14          8 $6.51  $6.82  $7.61  $8.00  $8.46  $9.04   $9.96   $33.97         215    4
  15          9 $6.65  $7.01  $7.73  $8.17  $8.63  $9.51   $10.58  $36.36         216    5
  16         10 $6.86  $7.05  $7.87  $8.40  $9.00  $10.17  $11.28  $38.61         217    4
  17                                                                              218    5
  18                                                                              220    4
  19                                                                              230    5
  20                                                                              239    4
  21                                                                              269 #N/A
  22                                                                              270    4

The data in cols K:L is part of the zone table from FedEx. The hyphenated ranges in col K were eliminated by selecting the column and replacing -* with nothing, which leaves simple numbers.

The formula in B3 is

=INDEX($B$7:$I$16, B2, VLOOKUP(--LEFT(B1, 3), $K$2:$L$223, 2) - 1)

I get a #REF when I input data into B1 & B2. A3 - B6 will be on a separate spreadsheet we use for quotes so how would I change the formula?

This is a reconfigured zone table I'll be using if it will make it easier.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>range</TD><TD>ZMin</TD><TD>ZMax</TD><TD style="TEXT-ALIGN: right">Zone</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>000-004</TD><TD style="TEXT-ALIGN: right">00000</TD><TD style="TEXT-ALIGN: right">00499</TD><TD style="TEXT-ALIGN: right">NA</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>005</TD><TD style="TEXT-ALIGN: right">00500</TD><TD style="TEXT-ALIGN: right">00599</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>010-046</TD><TD style="TEXT-ALIGN: right">01000</TD><TD style="TEXT-ALIGN: right">04699</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>047</TD><TD style="TEXT-ALIGN: right">04700</TD><TD style="TEXT-ALIGN: right">04799</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>048-129</TD><TD style="TEXT-ALIGN: right">04800</TD><TD style="TEXT-ALIGN: right">12999</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>130-132</TD><TD style="TEXT-ALIGN: right">13000</TD><TD style="TEXT-ALIGN: right">13299</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>133-139</TD><TD style="TEXT-ALIGN: right">13300</TD><TD style="TEXT-ALIGN: right">13999</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>140-179</TD><TD style="TEXT-ALIGN: right">14000</TD><TD style="TEXT-ALIGN: right">17999</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>180-199</TD><TD style="TEXT-ALIGN: right">18000</TD><TD style="TEXT-ALIGN: right">19999</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>200-212</TD><TD style="TEXT-ALIGN: right">20000</TD><TD style="TEXT-ALIGN: right">21299</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>213</TD><TD style="TEXT-ALIGN: right">21300</TD><TD style="TEXT-ALIGN: right">21399</TD><TD style="TEXT-ALIGN: right">NA</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>214</TD><TD style="TEXT-ALIGN: right">21400</TD><TD style="TEXT-ALIGN: right">21499</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>215</TD><TD style="TEXT-ALIGN: right">21500</TD><TD style="TEXT-ALIGN: right">21599</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>216</TD><TD style="TEXT-ALIGN: right">21600</TD><TD style="TEXT-ALIGN: right">21699</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>217</TD><TD style="TEXT-ALIGN: right">21700</TD><TD style="TEXT-ALIGN: right">21799</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>218-219</TD><TD style="TEXT-ALIGN: right">21800</TD><TD style="TEXT-ALIGN: right">21999</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>220-229</TD><TD style="TEXT-ALIGN: right">22000</TD><TD style="TEXT-ALIGN: right">22999</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>230-238</TD><TD style="TEXT-ALIGN: right">23000</TD><TD style="TEXT-ALIGN: right">23899</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>239-268</TD><TD style="TEXT-ALIGN: right">23900</TD><TD style="TEXT-ALIGN: right">26899</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD>269</TD><TD style="TEXT-ALIGN: right">26900</TD><TD style="TEXT-ALIGN: right">26999</TD><TD style="TEXT-ALIGN: right">NA</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD>270-274</TD><TD style="TEXT-ALIGN: right">27000</TD><TD style="TEXT-ALIGN: right">27499</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD>275-279</TD><TD style="TEXT-ALIGN: right">27500</TD><TD style="TEXT-ALIGN: right">27999</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">24</TD><TD>280-282</TD><TD style="TEXT-ALIGN: right">28000</TD><TD style="TEXT-ALIGN: right">28299</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">25</TD><TD>283-285</TD><TD style="TEXT-ALIGN: right">28300</TD><TD style="TEXT-ALIGN: right">28599</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">26</TD><TD>286-289</TD><TD style="TEXT-ALIGN: right">28600</TD><TD style="TEXT-ALIGN: right">28999</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">27</TD><TD>290-292</TD><TD style="TEXT-ALIGN: right">29000</TD><TD style="TEXT-ALIGN: right">29299</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">28</TD><TD>293</TD><TD style="TEXT-ALIGN: right">29300</TD><TD style="TEXT-ALIGN: right">29399</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">29</TD><TD>294-295</TD><TD style="TEXT-ALIGN: right">29400</TD><TD style="TEXT-ALIGN: right">29599</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">30</TD><TD>296-297</TD><TD style="TEXT-ALIGN: right">29600</TD><TD style="TEXT-ALIGN: right">29799</TD><TD style="TEXT-ALIGN: right">4</TD></TR></TBODY></TABLE>

zone2​

 

[shg]

This is a reconfigured zone table I'll be using if it will make it easier.

It doesn't make it easier:

The hyphenated ranges in col K were eliminated by selecting the column and replacing -* with nothing, which leaves simple numbers.

 

[gimage]

shg,
It works now. I did not eliminate the right part of the zone column properly. Thanks for your help.