I am trying to use a table to determine a rate for a specific Zip code with freight class.
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
So the user puts a zip(CK13) and class(CM39) and weight(CY39) and the formula should find the rate =INDEX('Outbound Rates'!$BO$4:$BT$688503,MATCH($CK$13&$CM$39,INDEX('Outbound Rates'!$BK$4:$BK$688503&'Outbound Rates'!$BL$4:$BL$688503,),0),MATCH($CY39,'Outbound Rates'!$BO$3:$BT$3))
So if the zip is 08753, class is 250, and weight is 500 the result should be 413.75 however it returns a result for a row below that is very similar but still very different. $356.98
<tbody>
</tbody>
I have all cells formatted as number and I know it has something to do with the leading zero in 08753
BK | BL | BM | BN | BO | BP | BQ | BR | BS | BT |
<tbody>
</tbody>
Zip | Class | AMC | MC | 300 | 500 | 1000 | 2000 | 5000 | 10000 |
<tbody>
</tbody>
08753 | 175 | 101 | 429.33 | 406.11 | 289.62 | 197.73 | 159.07 | 96.63 | 80.26 |
08753 | 200 | 101 | 429.33 | 464.13 | 330.99 | 225.97 | 181.79 | 110.43 | 91.73 |
08753 | 250 | 101 | 429.33 | 580.16 | 413.75 | 282.46 | 227.24 | 138.04 | 114.66 |
08753 | 300 | 101 | 429.33 | 696.19 | 496.49 | 338.96 | 272.69 | 165.65 | 137.59 |
<tbody>
</tbody>
So the user puts a zip(CK13) and class(CM39) and weight(CY39) and the formula should find the rate =INDEX('Outbound Rates'!$BO$4:$BT$688503,MATCH($CK$13&$CM$39,INDEX('Outbound Rates'!$BK$4:$BK$688503&'Outbound Rates'!$BL$4:$BL$688503,),0),MATCH($CY39,'Outbound Rates'!$BO$3:$BT$3))
So if the zip is 08753, class is 250, and weight is 500 the result should be 413.75 however it returns a result for a row below that is very similar but still very different. $356.98
87531 | 400 | 101 | 931.78 | 3090.06 | 2596.24 | 2124.84 | 1745.93 | 1565.79 | 1281.47 |
87531 | 500 | 101 | 931.78 | 3862.57 | 3245.29 | 2656.05 | 2182.41 | 1957.23 | 1601.84 |
87532 | 50 | 101 | 931.78 | 424.89 | 356.98 | 292.16 | 240.06 | 215.3 | 176.21 |
87532 | 55 | 101 | 931.78 | 459.65 | 386.19 | 316.07 | 259.71 | 232.9 | 190.62 |
<tbody>
</tbody>
I have all cells formatted as number and I know it has something to do with the leading zero in 08753