Getting Data from two sheets.

[pjkaphlen]

Hello Guys!

Can you help me in solving this excel problem?
I have a data in one sheet and I need to reference this data into another sheet.

Sheet 1

Weight per shipment	5.1960
Zone	6
Cost	$--------

<tbody>
</tbody>

Sheet 2

Weight	Zone1	Zone2	Zone3	Zone4	Zone5	Zone6
4	$0.1	$0.2	$0.3	$0.4	$0.5	$0.6
4.5	$1.1	$1.2	$1.3	$1.4	$1.5	$1.6
5	$2.5	$2.6	$2.7	$2.8	$2.9	$3.0
5.5	$3.5	$3.6	$3.7	$3.8	$3.9	$4.0

<tbody>
</tbody>

So let's say, on sheet 1, I have data for the weight and Zone. However the zone is not the exact value as was found in sheet 2. In this case, I will have to select 5.5 since the weight is already greater than 5.

Also, the zone is 6. Thus if i take the intersection of zone 6 and the weight of 5.5 I should be able to get $4.0 as cost. How should I input my formula in sheet 1 (Cost row) so that I will be able to get the correct cost for every ZOne and weight input?

Please help me with this guys. Thank you very much!

 

[Special-K99]

=INDEX(Sheet2!B2:G5,MATCH(Sheet1!B1,Sheet2!A2:A5,1)+1,MATCH("Zone"&Sheet1!B2,Sheet2!B1:G1,0))

 

[pjkaphlen]

Thanks! I was trying to use INDEX-MATCH but i just couldn't figure out how.

 

[pjkaphlen]

	Zone1	Zone2	Zone3	Zone4	Zone5	Zone6
5	$2.5	$2.6	$2.7	$2.8	$2.9	3.0
5.5	$3.5	$3.6	$4.7	$3.8	$3.9	$4.0
15	$10	$11	$12	$13	$14	$15
16	$16	$16	$18	$19	$20	$21
17	$23	$24	$25	$26	$27	$28
18	$29	$30	$31	$32	$33	$34

<tbody>
</tbody>

Ha! the programs works! However I tried to increase the range of sheet 2. Now, if I have weight in 16kg and Zone 1, then this means that the formula will always return the value of $23 dollars instead of $16, right? In which case, the value returned now will be incorrect.

Does it have something to do with the difference in columns A's incremental value?

I tried using the -1 match type, but unfortunately it doesn't return any value.