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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Weight per shipment[/TD]
[TD]5.1960[/TD]
[/TR]
[TR]
[TD]Zone[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Cost[/TD]
[TD]$--------[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Weight[/TD]
[TD]Zone1[/TD]
[TD]Zone2[/TD]
[TD]Zone3[/TD]
[TD]Zone4[/TD]
[TD]Zone5[/TD]
[TD]Zone6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]$0.1[/TD]
[TD]$0.2[/TD]
[TD]$0.3[/TD]
[TD]$0.4[/TD]
[TD]$0.5[/TD]
[TD]$0.6[/TD]
[/TR]
[TR]
[TD]4.5[/TD]
[TD]$1.1[/TD]
[TD]$1.2[/TD]
[TD]$1.3[/TD]
[TD]$1.4[/TD]
[TD]$1.5[/TD]
[TD]$1.6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]$2.5[/TD]
[TD]$2.6[/TD]
[TD]$2.7[/TD]
[TD]$2.8[/TD]
[TD]$2.9[/TD]
[TD]$3.0[/TD]
[/TR]
[TR]
[TD]5.5[/TD]
[TD]$3.5[/TD]
[TD]$3.6[/TD]
[TD]$3.7[/TD]
[TD]$3.8[/TD]
[TD]$3.9[/TD]
[TD]$4.0[/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Zone1[/TD]
[TD]Zone2[/TD]
[TD]Zone3[/TD]
[TD]Zone4[/TD]
[TD]Zone5[/TD]
[TD]Zone6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]$2.5[/TD]
[TD]$2.6[/TD]
[TD]$2.7[/TD]
[TD]$2.8[/TD]
[TD]$2.9[/TD]
[TD]3.0[/TD]
[/TR]
[TR]
[TD]5.5[/TD]
[TD]$3.5[/TD]
[TD]$3.6[/TD]
[TD]$4.7[/TD]
[TD]$3.8[/TD]
[TD]$3.9[/TD]
[TD]$4.0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]$10[/TD]
[TD]$11[/TD]
[TD]$12[/TD]
[TD]$13[/TD]
[TD]$14[/TD]
[TD]$15[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]$16[/TD]
[TD]$16[/TD]
[TD]$18[/TD]
[TD]$19[/TD]
[TD]$20[/TD]
[TD]$21[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]$23[/TD]
[TD]$24[/TD]
[TD]$25[/TD]
[TD]$26[/TD]
[TD]$27[/TD]
[TD]$28[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]$29[/TD]
[TD]$30[/TD]
[TD]$31[/TD]
[TD]$32[/TD]
[TD]$33[/TD]
[TD]$34[/TD]
[/TR]
</tbody>[/TABLE]


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.