return the NEXT higher match based on two criteria

[STban73]

OK, so I am new to "advanced" excel... I need help you guys! please!

I am trying to get the value for the shipping cost based on two criteria and need to obtain the highest value based on these two ranges. Example:

Table 1: m3 (gives the shipping costs based on the volume of the shipment):
up to :
1.5 m3 = $165
4 m3 = $185
7 m3 = $195
9 m3 = $205
12 m3 = $215
15 m3 = $225
18 m3 = $235

Table2: kg (gives the shipping costs based on the weight of the shipments):
up to:
500kg = $165
1500kg = $185
2500kg = $195
3500kg = $205
4500kg = $215
5500kg = $225
6500kg = $235
7500kg = $300
9500kg = $370

So, based on these tables, If I have a shipment of 1100kg and 5m3, it will give me the costs $195 based on table1 (because based on table 2, the result by kg is $185, and thus I should use the HIGHER value)

I am going crazy with this and looked in a lot of threads but can't figure it out.

Thanks for your help.

[Ace_XL]

See attached

[bebo021999]

Try this:

Please Login or Register  to view this content.

with your table 1 in A2:B8, table 2 in A11:B19, E2 is given Kg, E3 is given m3

[STban73]

amazing... thanks! can I ask you one last thing based on exactly that same sheet you sent? how to adjust that formula to include values bellow 500 and below 1.5? (mening if 1-500 should give $165 and 0.01-1.5 should give $165) thank you again!

[bebo021999]

Have you tried my solution in #3? See attachment.

[STban73]

Have you tried my solution in #3? See attachment.

Yes but actually the formula on this last book worked perfectly. Thanks to both!!