Hello guys, can you help me with these? I have these two sheets and from the data on Sheet 1 I would like to return a value at sheet 1 taken from Sheet 2. Notice the Zone and Weight values on Sheet 1 which has an equivalent Cost for every Zone-Weight pair.
I have used Index-match in order to return the value. However I also used "CEILING" function of 0.5. Which was only applicable for data the increments of 0.5 and not applicable to all the weight on Sheet 2. Notice that some have increments of 2 and some larger weights have an increment of 5.
here is the formula that I am using. What do you think can I add in order for this program to return the correct value?
=INDEX(Sheet2!B2:G5,MATCH(CEILING(Sheet1!B1,0.5),Sheet2!A2:A5,0),MATCH(Sheet1!B2,Sheet2!B1:G1,0))
P.S: Any fraction of a kg over the weight on sheet 2 should take the next higher weight rate. This means the the 30kg should be the reference for the case of the Weight given on Sheet 1.
Sheet 1
<tbody>
</tbody>
Sheet 2
<tbody>
</tbody>
All replies shall be greatly appreciated! Thanks in advance guys!
I have used Index-match in order to return the value. However I also used "CEILING" function of 0.5. Which was only applicable for data the increments of 0.5 and not applicable to all the weight on Sheet 2. Notice that some have increments of 2 and some larger weights have an increment of 5.
here is the formula that I am using. What do you think can I add in order for this program to return the correct value?
=INDEX(Sheet2!B2:G5,MATCH(CEILING(Sheet1!B1,0.5),Sheet2!A2:A5,0),MATCH(Sheet1!B2,Sheet2!B1:G1,0))
P.S: Any fraction of a kg over the weight on sheet 2 should take the next higher weight rate. This means the the 30kg should be the reference for the case of the Weight given on Sheet 1.
Sheet 1
Zone | 3 |
Weight | 25.1 |
Cost | $---.-- |
<tbody>
</tbody>
Sheet 2
weight/zone | 1 | 2 | 3 |
9 | $1.5 | $2.5 | $3.5 |
9.5 | $4.5 | $5.5 | $6.5 |
10 | $7.5 | $8.5 | $9.5 |
11 | $10.5 | $11.5 | $11.6 |
12 | $14.5 | $14.8 | $15.0 |
13 | $16.1 | $16.3 | $16.5 |
14 | $17.4 | $17.8 | $18.0 |
16 | $18.2 | $18.4 | $18.9 |
18 | $19.0 | $19.5 | $19.8 |
20 | $20.1 | $20.5 | $21.0 |
25 | $22.5 | $22.9 | $23.5 |
30 | $25.2 | $25.6 | $25.8 |
<tbody>
</tbody>
All replies shall be greatly appreciated! Thanks in advance guys!