Hello,
I am trying to run an Index match formula with a zip code plus class number and the formula I am using returns a value that doesnt match.
=INDEX('Outbound Rates'!$CI$3:$CN$3263,MATCH($CK$13&$CM$55,INDEX('Outbound Rates'!$CF$3:$CF$3263&'Outbound Rates'!$CH$3:$CH$3263,),1),MATCH($CY55,'Outbound Rates'!$CI$2:$CN$2,0)),""),"")
Ck13=08753
CM55=85
Data field= 300 500 1000 5000 10000 15000[TABLE="width: 576"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl77, width: 64, align: right"]7999[/TD]
[TD="width: 64, align: right"]80[/TD]
[TD="width: 64, align: right"]85[/TD]
[TD="width: 64, align: right"]175.89[/TD]
[TD="width: 64, align: right"]135.58[/TD]
[TD="width: 64, align: right"]100.34[/TD]
[TD="width: 64, align: right"]79.33[/TD]
[TD="width: 64, align: right"]52.82[/TD]
[TD="width: 64, align: right"]44.77[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]7999[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]360.57[/TD]
[TD="align: right"]277.92[/TD]
[TD="align: right"]205.67[/TD]
[TD="align: right"]162.61[/TD]
[TD="align: right"]112.71[/TD]
[TD="align: right"]95.52[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8199[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]205.3[/TD]
[TD="align: right"]159.75[/TD]
[TD="align: right"]120.96[/TD]
[TD="align: right"]98.85[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]60.86[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8199[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]420.83[/TD]
[TD="align: right"]327.47[/TD]
[TD="align: right"]247.96[/TD]
[TD="align: right"]202.62[/TD]
[TD="align: right"]153.62[/TD]
[TD="align: right"]129.83[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8299[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]214.38[/TD]
[TD="align: right"]166.88[/TD]
[TD="align: right"]126.83[/TD]
[TD="align: right"]103.98[/TD]
[TD="align: right"]77.52[/TD]
[TD="align: right"]65.79[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8299[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]439.47[/TD]
[TD="align: right"]342.09[/TD]
[TD="align: right"]259.99[/TD]
[TD="align: right"]213.14[/TD]
[TD="align: right"]165.38[/TD]
[TD="align: right"]140.35[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8399[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]212.54[/TD]
[TD="align: right"]165.38[/TD]
[TD="align: right"]125.9[/TD]
[TD="align: right"]103.01[/TD]
[TD="align: right"]76.44[/TD]
[TD="align: right"]64.64[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8399[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]435.68[/TD]
[TD="align: right"]339.02[/TD]
[TD="align: right"]258.08[/TD]
[TD="align: right"]211.16[/TD]
[TD="align: right"]163.09[/TD]
[TD="align: right"]137.92[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8499[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]214.38[/TD]
[TD="align: right"]166.88[/TD]
[TD="align: right"]126.83[/TD]
[TD="align: right"]103.98[/TD]
[TD="align: right"]77.52[/TD]
[TD="align: right"]65.79[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8499[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]439.47[/TD]
[TD="align: right"]342.09[/TD]
[TD="align: right"]259.99[/TD]
[TD="align: right"]213.14[/TD]
[TD="align: right"]165.38[/TD]
[TD="align: right"]140.35[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8699[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]196.7[/TD]
[TD="align: right"]153.17[/TD]
[TD="align: right"]115.28[/TD]
[TD="align: right"]92.85[/TD]
[TD="align: right"]66.26[/TD]
[TD="align: right"]56.09[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8699[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]403.21[/TD]
[TD="align: right"]313.99[/TD]
[TD="align: right"]236.32[/TD]
[TD="align: right"]190.33[/TD]
[TD="align: right"]141.38[/TD]
[TD="align: right"]119.66[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8799[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]198.51[/TD]
[TD="align: right"]154.31[/TD]
[TD="align: right"]116.63[/TD]
[TD="align: right"]93.9[/TD]
[TD="align: right"]67.55[/TD]
[TD="align: right"]57.06[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8799[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]406.94[/TD]
[TD="align: right"]316.32[/TD]
[TD="align: right"]239.06[/TD]
[TD="align: right"]192.49[/TD]
[TD="align: right"]144.13[/TD]
[TD="align: right"]121.73[/TD]
[/TR]
</tbody>[/TABLE]
Further down in the same field is the following zip which is the value that is returned instead of the value from the first field. I believe it has something to do with the zip begins with a zero.
[TABLE="width: 576"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl77, width: 64, align: right"]87375[/TD]
[TD="width: 64, align: right"]600[/TD]
[TD="width: 64, align: right"]9000[/TD]
[TD="width: 64, align: right"]74994[/TD]
[TD="width: 64, align: right"]40410[/TD]
[TD="width: 64, align: right"]34353[/TD]
[TD="width: 64, align: right"]28787[/TD]
[TD="width: 64, align: right"]23811[/TD]
[TD="width: 64, align: right"]21550[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]87499[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]74236[/TD]
[TD="align: right"]39826[/TD]
[TD="align: right"]33812[/TD]
[TD="align: right"]28289[/TD]
[TD="align: right"]23368[/TD]
[TD="align: right"]21096[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]87509[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]72316[/TD]
[TD="align: right"]38372[/TD]
[TD="align: right"]32455[/TD]
[TD="align: right"]27046[/TD]
[TD="align: right"]22244[/TD]
[TD="align: right"]19950[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]87587[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]83163[/TD]
[TD="align: right"]44128[/TD]
[TD="align: right"]37324[/TD]
[TD="align: right"]31103[/TD]
[TD="align: right"]25581[/TD]
[TD="align: right"]22943[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]87522[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]126553[/TD]
[TD="align: right"]67151[/TD]
[TD="align: right"]56797[/TD]
[TD="align: right"]47331[/TD]
[TD="align: right"]38927[/TD]
[TD="align: right"]34913[/TD]
[/TR]
</tbody>[/TABLE]
Please help
I am trying to run an Index match formula with a zip code plus class number and the formula I am using returns a value that doesnt match.
=INDEX('Outbound Rates'!$CI$3:$CN$3263,MATCH($CK$13&$CM$55,INDEX('Outbound Rates'!$CF$3:$CF$3263&'Outbound Rates'!$CH$3:$CH$3263,),1),MATCH($CY55,'Outbound Rates'!$CI$2:$CN$2,0)),""),"")
Ck13=08753
CM55=85
Data field= 300 500 1000 5000 10000 15000[TABLE="width: 576"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl77, width: 64, align: right"]7999[/TD]
[TD="width: 64, align: right"]80[/TD]
[TD="width: 64, align: right"]85[/TD]
[TD="width: 64, align: right"]175.89[/TD]
[TD="width: 64, align: right"]135.58[/TD]
[TD="width: 64, align: right"]100.34[/TD]
[TD="width: 64, align: right"]79.33[/TD]
[TD="width: 64, align: right"]52.82[/TD]
[TD="width: 64, align: right"]44.77[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]7999[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]360.57[/TD]
[TD="align: right"]277.92[/TD]
[TD="align: right"]205.67[/TD]
[TD="align: right"]162.61[/TD]
[TD="align: right"]112.71[/TD]
[TD="align: right"]95.52[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8199[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]205.3[/TD]
[TD="align: right"]159.75[/TD]
[TD="align: right"]120.96[/TD]
[TD="align: right"]98.85[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]60.86[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8199[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]420.83[/TD]
[TD="align: right"]327.47[/TD]
[TD="align: right"]247.96[/TD]
[TD="align: right"]202.62[/TD]
[TD="align: right"]153.62[/TD]
[TD="align: right"]129.83[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8299[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]214.38[/TD]
[TD="align: right"]166.88[/TD]
[TD="align: right"]126.83[/TD]
[TD="align: right"]103.98[/TD]
[TD="align: right"]77.52[/TD]
[TD="align: right"]65.79[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8299[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]439.47[/TD]
[TD="align: right"]342.09[/TD]
[TD="align: right"]259.99[/TD]
[TD="align: right"]213.14[/TD]
[TD="align: right"]165.38[/TD]
[TD="align: right"]140.35[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8399[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]212.54[/TD]
[TD="align: right"]165.38[/TD]
[TD="align: right"]125.9[/TD]
[TD="align: right"]103.01[/TD]
[TD="align: right"]76.44[/TD]
[TD="align: right"]64.64[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8399[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]435.68[/TD]
[TD="align: right"]339.02[/TD]
[TD="align: right"]258.08[/TD]
[TD="align: right"]211.16[/TD]
[TD="align: right"]163.09[/TD]
[TD="align: right"]137.92[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8499[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]214.38[/TD]
[TD="align: right"]166.88[/TD]
[TD="align: right"]126.83[/TD]
[TD="align: right"]103.98[/TD]
[TD="align: right"]77.52[/TD]
[TD="align: right"]65.79[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8499[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]439.47[/TD]
[TD="align: right"]342.09[/TD]
[TD="align: right"]259.99[/TD]
[TD="align: right"]213.14[/TD]
[TD="align: right"]165.38[/TD]
[TD="align: right"]140.35[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8699[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]196.7[/TD]
[TD="align: right"]153.17[/TD]
[TD="align: right"]115.28[/TD]
[TD="align: right"]92.85[/TD]
[TD="align: right"]66.26[/TD]
[TD="align: right"]56.09[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8699[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]403.21[/TD]
[TD="align: right"]313.99[/TD]
[TD="align: right"]236.32[/TD]
[TD="align: right"]190.33[/TD]
[TD="align: right"]141.38[/TD]
[TD="align: right"]119.66[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8799[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]198.51[/TD]
[TD="align: right"]154.31[/TD]
[TD="align: right"]116.63[/TD]
[TD="align: right"]93.9[/TD]
[TD="align: right"]67.55[/TD]
[TD="align: right"]57.06[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]8799[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]406.94[/TD]
[TD="align: right"]316.32[/TD]
[TD="align: right"]239.06[/TD]
[TD="align: right"]192.49[/TD]
[TD="align: right"]144.13[/TD]
[TD="align: right"]121.73[/TD]
[/TR]
</tbody>[/TABLE]
Further down in the same field is the following zip which is the value that is returned instead of the value from the first field. I believe it has something to do with the zip begins with a zero.
[TABLE="width: 576"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl77, width: 64, align: right"]87375[/TD]
[TD="width: 64, align: right"]600[/TD]
[TD="width: 64, align: right"]9000[/TD]
[TD="width: 64, align: right"]74994[/TD]
[TD="width: 64, align: right"]40410[/TD]
[TD="width: 64, align: right"]34353[/TD]
[TD="width: 64, align: right"]28787[/TD]
[TD="width: 64, align: right"]23811[/TD]
[TD="width: 64, align: right"]21550[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]87499[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]74236[/TD]
[TD="align: right"]39826[/TD]
[TD="align: right"]33812[/TD]
[TD="align: right"]28289[/TD]
[TD="align: right"]23368[/TD]
[TD="align: right"]21096[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]87509[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]72316[/TD]
[TD="align: right"]38372[/TD]
[TD="align: right"]32455[/TD]
[TD="align: right"]27046[/TD]
[TD="align: right"]22244[/TD]
[TD="align: right"]19950[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]87587[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]83163[/TD]
[TD="align: right"]44128[/TD]
[TD="align: right"]37324[/TD]
[TD="align: right"]31103[/TD]
[TD="align: right"]25581[/TD]
[TD="align: right"]22943[/TD]
[/TR]
[TR]
[TD="class: xl77, align: right"]87522[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]126553[/TD]
[TD="align: right"]67151[/TD]
[TD="align: right"]56797[/TD]
[TD="align: right"]47331[/TD]
[TD="align: right"]38927[/TD]
[TD="align: right"]34913[/TD]
[/TR]
</tbody>[/TABLE]
Please help