I am trying to use a table to determine a rate for a specific Zip code with freight class.
[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl78, width: 64"]BK[/TD]
[TD="class: xl78, width: 64"]BL[/TD]
[TD="class: xl78, width: 64"]BM[/TD]
[TD="class: xl78, width: 64"]BN[/TD]
[TD="class: xl78, width: 64"]BO[/TD]
[TD="class: xl78, width: 64"]BP[/TD]
[TD="class: xl78, width: 64"]BQ[/TD]
[TD="class: xl78, width: 64"]BR[/TD]
[TD="class: xl78, width: 64"]BS[/TD]
[TD="class: xl78, width: 64"]BT[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 640"]
<tbody>[TR]
[TD="width: 64"] Zip[/TD]
[TD="width: 64"]Class[/TD]
[TD="width: 64"] AMC[/TD]
[TD="width: 64"] MC[/TD]
[TD="width: 64, align: right"]300[/TD]
[TD="width: 64, align: right"]500[/TD]
[TD="width: 64, align: right"]1000[/TD]
[TD="width: 64, align: right"]2000[/TD]
[TD="width: 64, align: right"]5000[/TD]
[TD="width: 64, align: right"]10000[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl78, width: 64"]08753[/TD]
[TD="width: 64, align: right"]175[/TD]
[TD="width: 64, align: right"]101[/TD]
[TD="width: 64, align: right"]429.33[/TD]
[TD="width: 64, align: right"]406.11[/TD]
[TD="width: 64, align: right"]289.62[/TD]
[TD="width: 64, align: right"]197.73[/TD]
[TD="width: 64, align: right"]159.07[/TD]
[TD="width: 64, align: right"]96.63[/TD]
[TD="width: 64, align: right"]80.26[/TD]
[/TR]
[TR]
[TD="class: xl78"]08753[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]429.33[/TD]
[TD="align: right"]464.13[/TD]
[TD="align: right"]330.99[/TD]
[TD="align: right"]225.97[/TD]
[TD="align: right"]181.79[/TD]
[TD="align: right"]110.43[/TD]
[TD="align: right"]91.73[/TD]
[/TR]
[TR]
[TD="class: xl78"]08753[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]429.33[/TD]
[TD="align: right"]580.16[/TD]
[TD="align: right"]413.75[/TD]
[TD="align: right"]282.46[/TD]
[TD="align: right"]227.24[/TD]
[TD="align: right"]138.04[/TD]
[TD="align: right"]114.66[/TD]
[/TR]
[TR]
[TD="class: xl78"]08753[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]429.33[/TD]
[TD="align: right"]696.19[/TD]
[TD="align: right"]496.49[/TD]
[TD="align: right"]338.96[/TD]
[TD="align: right"]272.69[/TD]
[TD="align: right"]165.65[/TD]
[TD="align: right"]137.59[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl78, width: 64"]87531[/TD]
[TD="width: 64, align: right"]400[/TD]
[TD="width: 64, align: right"]101[/TD]
[TD="width: 64, align: right"]931.78[/TD]
[TD="width: 64, align: right"]3090.06[/TD]
[TD="width: 64, align: right"]2596.24[/TD]
[TD="width: 64, align: right"]2124.84[/TD]
[TD="width: 64, align: right"]1745.93[/TD]
[TD="width: 64, align: right"]1565.79[/TD]
[TD="width: 64, align: right"]1281.47[/TD]
[/TR]
[TR]
[TD="class: xl78"]87531[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]931.78[/TD]
[TD="align: right"]3862.57[/TD]
[TD="align: right"]3245.29[/TD]
[TD="align: right"]2656.05[/TD]
[TD="align: right"]2182.41[/TD]
[TD="align: right"]1957.23[/TD]
[TD="align: right"]1601.84[/TD]
[/TR]
[TR]
[TD="class: xl78"]87532[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]931.78[/TD]
[TD="align: right"]424.89[/TD]
[TD="align: right"]356.98[/TD]
[TD="align: right"]292.16[/TD]
[TD="align: right"]240.06[/TD]
[TD="align: right"]215.3[/TD]
[TD="align: right"]176.21[/TD]
[/TR]
[TR]
[TD="class: xl78"]87532[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]931.78[/TD]
[TD="align: right"]459.65[/TD]
[TD="align: right"]386.19[/TD]
[TD="align: right"]316.07[/TD]
[TD="align: right"]259.71[/TD]
[TD="align: right"]232.9[/TD]
[TD="align: right"]190.62[/TD]
[/TR]
</tbody>[/TABLE]
I have all cells formatted as number and I know it has something to do with the leading zero in 08753
[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl78, width: 64"]BK[/TD]
[TD="class: xl78, width: 64"]BL[/TD]
[TD="class: xl78, width: 64"]BM[/TD]
[TD="class: xl78, width: 64"]BN[/TD]
[TD="class: xl78, width: 64"]BO[/TD]
[TD="class: xl78, width: 64"]BP[/TD]
[TD="class: xl78, width: 64"]BQ[/TD]
[TD="class: xl78, width: 64"]BR[/TD]
[TD="class: xl78, width: 64"]BS[/TD]
[TD="class: xl78, width: 64"]BT[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 640"]
<tbody>[TR]
[TD="width: 64"] Zip[/TD]
[TD="width: 64"]Class[/TD]
[TD="width: 64"] AMC[/TD]
[TD="width: 64"] MC[/TD]
[TD="width: 64, align: right"]300[/TD]
[TD="width: 64, align: right"]500[/TD]
[TD="width: 64, align: right"]1000[/TD]
[TD="width: 64, align: right"]2000[/TD]
[TD="width: 64, align: right"]5000[/TD]
[TD="width: 64, align: right"]10000[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl78, width: 64"]08753[/TD]
[TD="width: 64, align: right"]175[/TD]
[TD="width: 64, align: right"]101[/TD]
[TD="width: 64, align: right"]429.33[/TD]
[TD="width: 64, align: right"]406.11[/TD]
[TD="width: 64, align: right"]289.62[/TD]
[TD="width: 64, align: right"]197.73[/TD]
[TD="width: 64, align: right"]159.07[/TD]
[TD="width: 64, align: right"]96.63[/TD]
[TD="width: 64, align: right"]80.26[/TD]
[/TR]
[TR]
[TD="class: xl78"]08753[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]429.33[/TD]
[TD="align: right"]464.13[/TD]
[TD="align: right"]330.99[/TD]
[TD="align: right"]225.97[/TD]
[TD="align: right"]181.79[/TD]
[TD="align: right"]110.43[/TD]
[TD="align: right"]91.73[/TD]
[/TR]
[TR]
[TD="class: xl78"]08753[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]429.33[/TD]
[TD="align: right"]580.16[/TD]
[TD="align: right"]413.75[/TD]
[TD="align: right"]282.46[/TD]
[TD="align: right"]227.24[/TD]
[TD="align: right"]138.04[/TD]
[TD="align: right"]114.66[/TD]
[/TR]
[TR]
[TD="class: xl78"]08753[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]429.33[/TD]
[TD="align: right"]696.19[/TD]
[TD="align: right"]496.49[/TD]
[TD="align: right"]338.96[/TD]
[TD="align: right"]272.69[/TD]
[TD="align: right"]165.65[/TD]
[TD="align: right"]137.59[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl78, width: 64"]87531[/TD]
[TD="width: 64, align: right"]400[/TD]
[TD="width: 64, align: right"]101[/TD]
[TD="width: 64, align: right"]931.78[/TD]
[TD="width: 64, align: right"]3090.06[/TD]
[TD="width: 64, align: right"]2596.24[/TD]
[TD="width: 64, align: right"]2124.84[/TD]
[TD="width: 64, align: right"]1745.93[/TD]
[TD="width: 64, align: right"]1565.79[/TD]
[TD="width: 64, align: right"]1281.47[/TD]
[/TR]
[TR]
[TD="class: xl78"]87531[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]931.78[/TD]
[TD="align: right"]3862.57[/TD]
[TD="align: right"]3245.29[/TD]
[TD="align: right"]2656.05[/TD]
[TD="align: right"]2182.41[/TD]
[TD="align: right"]1957.23[/TD]
[TD="align: right"]1601.84[/TD]
[/TR]
[TR]
[TD="class: xl78"]87532[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]931.78[/TD]
[TD="align: right"]424.89[/TD]
[TD="align: right"]356.98[/TD]
[TD="align: right"]292.16[/TD]
[TD="align: right"]240.06[/TD]
[TD="align: right"]215.3[/TD]
[TD="align: right"]176.21[/TD]
[/TR]
[TR]
[TD="class: xl78"]87532[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]931.78[/TD]
[TD="align: right"]459.65[/TD]
[TD="align: right"]386.19[/TD]
[TD="align: right"]316.07[/TD]
[TD="align: right"]259.71[/TD]
[TD="align: right"]232.9[/TD]
[TD="align: right"]190.62[/TD]
[/TR]
</tbody>[/TABLE]
I have all cells formatted as number and I know it has something to do with the leading zero in 08753