Index and Match across different sheets

[questforexcel]

Hello,

I have a 2 D data (rows and columns) on Sheet 1 and I have specific values in Sheet 2.

I want my index match formula to return select from the range in Sheet 1 and the rows and columns in Sheet 2 to return the value specific to that combination.

=INDEX('Zone & Pricing Chart'!$L$5:$V$10,MATCH('Shipping Cost'!I7,'Zone & Pricing Chart'!$L$6:$L$10,0),MATCH('Shipping Cost'!H7,'Zone & Pricing Chart'!$L$5:$V$5,0))

Below is the formula that I used. But when I use it , it gives the following error: "#N/A"

Below is the data on my sheet 1.

[TABLE="width: 534"]
<tbody>[TR]
[TD][/TD]
[TD]Up To:[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]0.1[/TD]
[TD]$6.4[/TD]
[TD]$6.4[/TD]
[TD]$6.8[/TD]
[TD]$6.9[/TD]
[TD]$7.0[/TD]
[TD]$7.1[/TD]
[TD]$7.3[/TD]
[TD]$7.5[/TD]
[TD]$9.7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.2[/TD]
[TD]$6.8[/TD]
[TD]$6.8[/TD]
[TD]$7.1[/TD]
[TD]$7.2[/TD]
[TD]$7.5[/TD]
[TD]$7.9[/TD]
[TD]$8.2[/TD]
[TD]$8.5[/TD]
[TD]$11.3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.3[/TD]
[TD]$7.2[/TD]
[TD]$7.2[/TD]
[TD]$7.4[/TD]
[TD]$7.5[/TD]
[TD]$8.2[/TD]
[TD]$9.5[/TD]
[TD]$10.3[/TD]
[TD]$10.8[/TD]
[TD]$15.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.4[/TD]
[TD]$7.3[/TD]
[TD]$7.3[/TD]
[TD]$7.7[/TD]
[TD]$8.3[/TD]
[TD]$9.1[/TD]
[TD]$11.5[/TD]
[TD]$12.5[/TD]
[TD]$14.2[/TD]
[TD]$19.4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]0.5[/TD]
[TD]$7.4[/TD]
[TD]$7.4[/TD]
[TD]$8.1[/TD]
[TD]$8.8[/TD]
[TD]$10.2[/TD]
[TD]$13.8[/TD]
[TD]$15.4[/TD]
[TD]$17.5[/TD]
[TD]$24.1[/TD]
[/TR]
</tbody>[/TABLE]


I saw on some posts , mentions to use Indirect method, when I tried using it, it gave the error "#VALUE".

Could you please help me fix thiss issue.

Thank you

 

[DanteAmor]

I do not understand very well how you have your data in sheet1, but if you have the following:

Sheet Zone & Pricing Chart

[TABLE="class: grid, width: 600"]
<colgroup><col width="80" span="10" style="width:60pt"> </colgroup><tbody>[TR]
[TD="width: 80"][/TD]
[TD="width: 80, align: center"]A[/TD]
[TD="width: 80, align: center"]...[/TD]
[TD="width: 80, align: center"]L[/TD]
[TD="width: 80, align: center"]M[/TD]
[TD="width: 80, align: center"]N[/TD]
[TD="width: 80, align: center"]O[/TD]
[TD="width: 80, align: center"]P[/TD]
[TD="width: 80, align: center"]Q[/TD]
[TD="width: 80, align: center"]R[/TD]
[TD="width: 80, align: center"]S[/TD]
[TD="width: 80, align: center"]T[/TD]
[TD="width: 80, align: center"]U[/TD]
[TD="width: 80, align: center"]V[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]...[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]$6.40[/TD]
[TD="align: right"]$6.40[/TD]
[TD="align: right"]$6.80[/TD]
[TD="align: right"]$6.90[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$7.10[/TD]
[TD="align: right"]$7.30[/TD]
[TD="align: right"]$7.50[/TD]
[TD="align: right"]$9.70[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]$6.80[/TD]
[TD="align: right"]$6.80[/TD]
[TD="align: right"]$7.10[/TD]
[TD="align: right"]$7.20[/TD]
[TD="align: right"]$900.00[/TD]
[TD="align: right"]$7.90[/TD]
[TD="align: right"]$8.20[/TD]
[TD="align: right"]$8.50[/TD]
[TD="align: right"]$11.30[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]$7.20[/TD]
[TD="align: right"]$7.20[/TD]
[TD="align: right"]$7.40[/TD]
[TD="align: right"]$7.50[/TD]
[TD="align: right"]$8.20[/TD]
[TD="align: right"]$9.50[/TD]
[TD="align: right"]$10.30[/TD]
[TD="align: right"]$10.80[/TD]
[TD="align: right"]$15.20[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]$7.30[/TD]
[TD="align: right"]$7.30[/TD]
[TD="align: right"]$7.70[/TD]
[TD="align: right"]$8.30[/TD]
[TD="align: right"]$9.10[/TD]
[TD="align: right"]$11.50[/TD]
[TD="align: right"]$12.50[/TD]
[TD="align: right"]$14.20[/TD]
[TD="align: right"]$19.40[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]$7.40[/TD]
[TD="align: right"]$7.40[/TD]
[TD="align: right"]$8.10[/TD]
[TD="align: right"]$8.80[/TD]
[TD="align: right"]$10.20[/TD]
[TD="align: right"]$13.80[/TD]
[TD="align: right"]$15.40[/TD]
[TD="align: right"]$17.50[/TD]
[TD="align: right"]$24.10[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet Shipping Cost

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$900.00[/TD]
[/TR]
</tbody>[/TABLE]

The coordinate of 5 and .02 is 900. Then try the following formula and review your data.

[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]I8[/TD]
[TD]=INDIX('Zone & Pricing Chart'!L6:V10,MATCH(I7,'Zone & Pricing Chart'!L6:L10,0),MATCH(H7,'Zone & Pricing Chart'!L5:U5,0))[/TD]
[/TR]
</tbody>[/TABLE]

 

[questforexcel]

Thank you so much for your quick reply.

 

[DanteAmor]

I'm glad to help you