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.

	Up To:	1	2	3	4	5	6	7	8	9
A	0.1	$6.4	$6.4	$6.8	$6.9	$7.0	$7.1	$7.3	$7.5	$9.7
	0.2	$6.8	$6.8	$7.1	$7.2	$7.5	$7.9	$8.2	$8.5	$11.3
	0.3	$7.2	$7.2	$7.4	$7.5	$8.2	$9.5	$10.3	$10.8	$15.2
	0.4	$7.3	$7.3	$7.7	$8.3	$9.1	$11.5	$12.5	$14.2	$19.4
B	0.5	$7.4	$7.4	$8.1	$8.8	$10.2	$13.8	$15.4	$17.5	$24.1

<tbody>
</tbody>

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

	A	...	L	M	N	O	P	Q	R	S	T	U	V
1
...
5				1	2	3	4	5	6	7	8	9
6			0.1	$6.40	$6.40	$6.80	$6.90	$7.00	$7.10	$7.30	$7.50	$9.70
7			0.2	$6.80	$6.80	$7.10	$7.20	$900.00	$7.90	$8.20	$8.50	$11.30
8			0.3	$7.20	$7.20	$7.40	$7.50	$8.20	$9.50	$10.30	$10.80	$15.20
9			0.4	$7.30	$7.30	$7.70	$8.30	$9.10	$11.50	$12.50	$14.20	$19.40
10			0.5	$7.40	$7.40	$8.10	$8.80	$10.20	$13.80	$15.40	$17.50	$24.10

<colgroup><col width="80" span="10" style="width:60pt"> </colgroup><tbody>
</tbody>

Sheet Shipping Cost

	A	...	H	I
1
...
7			5	0.2
8				$900.00

<tbody>
</tbody>

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

Cell	Formula
I8	=INDIX('Zone & Pricing Chart'!L6:V10,MATCH(I7,'Zone & Pricing Chart'!L6:L10,0),MATCH(H7,'Zone & Pricing Chart'!L5:U5,0))

<tbody>
</tbody>

 

[questforexcel]

Thank you so much for your quick reply.

 

[DanteAmor]

I'm glad to help you