Rounding table and Vlookup?

Hey,

I have an if formulae that rounds a value depending on the range it falls between. Its very long and messy and i would like to have the capability of changing the table so i think a vlookup would be best suited. is it possible to run a vlookup without an exact match i.e. values in a given range?

If 15 < qty < 100, round up to nearest 5.
If 100 < qty < 300, round up to nearest 10
If 300 < qty < 500, round up to nearest 50
If qty > 500, round up to nearest 100.

Thanks,

Adam

Also unfortunately, i know how to upload excel files but it is not working at the moment for me. so i cant show an example unfortunately =(

Hi, adamire!

If you have the number in A1, try this formula:
=MROUND(A1,LOOKUP(A1,{15;100;300;500},{5;10;50;100}))

Blessings!

Thanks so much for the reply.

That is nearly perfect, is it possible to round up though? or do you have to use the mround funtion?

For rounding up, use

=CEILING(A1,LOOKUP(A1,{0,15,100,300,500},{1,5,10,50,100}))
=CEILING(A1,LOOKUP(A1,$I$1:$I$5,$J$1:$J$5))

That is so awesome thanks.