How to perform two way look-up across different sheets?

[xlmaniac]

Dear All,
I would like to perform a 2 way lookup across 2 different sheets.
In the Sheet1 I do have the following Store-wise Weight-Slab-wise rate matrix spread across A2:E6(from where the look-up has to happen).
Sheet1

*	A	B	C	D	E
1	*	Weight Matrix Kg(As Per Slabs)
2	Store	50	100	150	200
3	A	$1,000	$1,200	$1,400	$1,600
4	B	$1,500	$1,800	$2,100	$2,400
5	C	$2,000	$2,400	$2,800	$3,200
6	D	$2,500	$3,000	$3,500	$4,000

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 41px;"><col style="width: 46px;"><col style="width: 46px;"><col style="width: 46px;"><col style="width: 58px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
The store names are across the column A(A3:A6) and the rates across these 4 stores under different slabs are present across B3:E6.
For example,The applicable rate for 50 Kg for store A is $1000 and the same for 100kg for storeA is $1200 and so on.
Now based on this matrix in Sheet1 I want to calculate the freight rate based on store & weight-slab in Sheet2:-

Sheet2

*	A	B	C
1	Store	Weight(Kg)	Rate(Desired Result)
2	A	50	$1,000
3	A	150	$1,400
4	B	200	$2,400
5	C	150	$2,800
6	D	200	$4,000

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 38px;"><col style="width: 72px;"><col style="width: 129px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
To further explain, I want the formula across C2:C6(in Sheet2) to match the store and the weight (from Sheet1) and subsequently pick up the Rate from the data set spread across A2:E6(from Sheet1).
Would be grateful if somebody helps with the correct formula.
Regards

 

[Robert Mika]

Dear All,
I would like to perform a 2 way lookup across 2 different sheets.
In the Sheet1 I do have the following Store-wise Weight-Slab-wise rate matrix spread across A2:E6(from where the look-up has to happen).
Sheet1

*	A	B	C	D	E
1	*	Weight Matrix Kg(As Per Slabs)
2	Store	50	100	150	200
3	A	$1,000	$1,200	$1,400	$1,600
4	B	$1,500	$1,800	$2,100	$2,400
5	C	$2,000	$2,400	$2,800	$3,200
6	D	$2,500	$3,000	$3,500	$4,000

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
The store names are across the column A(A3:A6) and the rates across these 4 stores under different slabs are present across B3:E6.
For example,The applicable rate for 50 Kg for store A is $1000 and the same for 100kg for storeA is $1200 and so on.
Now based on this matrix in Sheet1 I want to calculate the freight rate based on store & weight-slab in Sheet2:-

Sheet2

*	A	B	C
1	Store	Weight(Kg)	Rate(Desired Result)
2	A	50	$1,000
3	A	150	$1,400
4	B	200	$2,400
5	C	150	$2,800
6	D	200	$4,000

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
To further explain, I want the formula across C2:C6(in Sheet2) to match the store and the weight (from Sheet1) and subsequently pick up the Rate from the data set spread across A2:E6(from Sheet1).
Would be grateful if somebody helps with the correct formula.
Regards

C2 down:
=INDEX(Sheet1!$B$3:$E$6,MATCH(A2,Sheet1!$A$3:$A$6,0),MATCH(B2,Sheet1!$B$2:$E$2,0))

 

[xlmaniac]

C2 down:
=INDEX(Sheet1!$B$3:$E$6,MATCH(A2,Sheet1!$A$3:$A$6,0),MATCH(B2,Sheet1!$B$2:$E$2,0))

Dear Mr Mika,
Thank you so much for helping me yet again with your mesmerising solution.
Really appreciate the same.
I feel honoured to learn excel from the gurus like you.
One last question, incase the weight of the shipment falls between 2 slabs and I would like to match basis on the next higher slab then what will be the formula?
Thanks once again for all your support

 

[Robert Mika]

Change the second row in Sheet1 to read
0,50,100,150 and then in Sheet2

=INDEX(Sheet1!$B$3:$E$6,MATCH(A2,Sheet1!$A$3:$A$6,0),MATCH(B2,Sheet1!$B$2:$E$2,1))

 

[xlmaniac]

Change the second row in Sheet1 to read
0,50,100,150 and then in Sheet2

=INDEX(Sheet1!$B$3:$E$6,MATCH(A2,Sheet1!$A$3:$A$6,0),MATCH(B2,Sheet1!$B$2:$E$2,1))

Dear Mr Mika,
You have created a WOW for me!!!
Please accept my sincere gratitude for the same....
Regards

 

[Robert Mika]

Dear Mr Mika,
You have created a WOW for me!!!
Please accept my sincere gratitude for the same....
Regards

You are welcome.