Vlookup that Roundup to the higher value or Index and Match

[RTDubb]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cubic[/TD]
[TD]Local $[/TD]
[TD]Outer Area $[/TD]
[TD]ShortHaul $[/TD]
[TD]Longhaul $[/TD]
[TD]InterIsland $[/TD]
[/TR]
[TR]
[TD]0.003[/TD]
[TD]2.00[/TD]
[TD]4.00[/TD]
[TD]6.00[/TD]
[TD]8.00[/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]0.005
[/TD]
[TD]2.20[/TD]
[TD]4.40[/TD]
[TD]6.60[/TD]
[TD]8.80[/TD]
[TD]11.00[/TD]
[/TR]
[TR]
[TD]0.008
[/TD]
[TD]2.40[/TD]
[TD]4.80[/TD]
[TD]7.20[/TD]
[TD]9.60[/TD]
[TD]12.00[/TD]
[/TR]
[TR]
[TD]0.01[/TD]
[TD]2.60[/TD]
[TD]5.20[/TD]
[TD]7.80[/TD]
[TD]10.40[/TD]
[TD]13.00[/TD]
[/TR]
[TR]
[TD]0.012[/TD]
[TD]2.80[/TD]
[TD]5.60[/TD]
[TD]8.40[/TD]
[TD]11.20[/TD]
[TD]14.00[/TD]
[/TR]
[TR]
[TD]0.016[/TD]
[TD]3.00[/TD]
[TD]6.00[/TD]
[TD]9.00[/TD]
[TD]12.00[/TD]
[TD]15.00[/TD]
[/TR]
[TR]
[TD]0.02[/TD]
[TD]3.20[/TD]
[TD]6.40[/TD]
[TD]9.60[/TD]
[TD]12.80[/TD]
[TD]16.00[/TD]
[/TR]
</tbody>[/TABLE]

Hi, I am new here and have spent a bit of time looking for the best way to obtain the right answer.

The above table is charges our courier applies for tickets to different areas we send orders.

I already have another worksheet with a cell that is determining the correct area (IE. local, etc) but also need to enter into the next cell a number up to 3 decimal places. From there I need to get a result of the higher value. IE. 0.006 local = $2.40 charge OR 0.011 longhaul = $11.20

I have tried index/match but it needs an exact match. I have tried vlookup with TRUE but it rounds down. I have tried roundup on the cell figure first and then vlookup but the increments in the courier rate schedule don't match.

Does anyone have any ideas of what formula I can use? I'm going crazy

 

[Gerald Higgins]

Hi, welcome to the board.

Match does not need an exact match, it can be used to find the smallest value that is greater than the target value, which I think is what you want here.
But for that to work, you need to sort your data into the reverse order of what you have here, so that 0.02 is at the top.
I think re-sorting your data might be the easiest option.

Alternatively, a more complex option might be to say, IF the target value is matched exactly, lookup the relevant value, and if not, use the OFFSET function to pull the next value.
I assume it's possible that the target value might be matched exactly.

 

[RTDubb]

Just to make it easier, the cells on the other worksheet (CHECK CHARGE) are A1 = cubic measure up to 3 decimal places AND B1 = Area. The above table is in another worksheet (COURIER RATES).

I applied this formula =INDEX('COURIER RATES'!$B$2:$F$17,MATCH('CHECK CHARGE'!A1,'COURIER RATES'!$A$2:$A$17,0),MATCH('CHECK CHARGE'!B1,'COURIER RATES'!$B$1:$F$1,0))

 

[RTDubb]

Thanks Gerald. I tried reversing the order of the courier rate cubics but received an #N/A. My formula only works if the cubic measurement entered exactly matches the cubic on the table.

 

[Gerald Higgins]

Try changing the final argument in the MATCH function from 0 to 1 or -1, off the top of my head I can't remember which one, but play around with it and you should find it.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr][tr][td]

1​

[/td][td] Cubic[/td][td] Local $[/td][td] Outer Area $[/td][td] ShortHaul $[/td][td] Longhaul $[/td][td] InterIsland $[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td] 0.003[/td][td] 2[/td][td] 4[/td][td] 6[/td][td] 8[/td][td] 10[/td][td][/td][td] local[/td][td] 0.006[/td][td] 2.4[/td][/tr]
[tr][td]

3​

[/td][td] 0.005[/td][td] 2.2[/td][td] 4.4[/td][td] 6.6[/td][td] 8.8[/td][td] 11[/td][td][/td][td] longhaul[/td][td] 0.011[/td][td] 11.2[/td][/tr]
[tr][td]

4​

[/td][td] 0.008[/td][td] 2.4[/td][td] 4.8[/td][td] 7.2[/td][td] 9.6[/td][td] 12[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td] 0.01[/td][td] 2.6[/td][td] 5.2[/td][td] 7.8[/td][td] 10.4[/td][td] 13[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td] 0.012[/td][td] 2.8[/td][td] 5.6[/td][td] 8.4[/td][td] 11.2[/td][td] 14[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td] 0.016[/td][td] 3[/td][td] 6[/td][td] 9[/td][td] 12[/td][td] 15[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td] 0.02[/td][td] 3.2[/td][td] 6.4[/td][td] 9.6[/td][td] 12.8[/td][td] 16[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In J2 enter and copy down:

=INDEX($A$2:$F$8,MATCH(I2,$A$2:$A$8,1)+(LOOKUP(I2,$A$2:$A$8) < I2),MATCH(H2&"*",$A$1:$F$1,0))