vlookup - Search related range for number between 2 values (vba, Excel) -

i'm learning vba , have excel table looks this:

range |max | min| discount%  r1    |0   |   9|         0                                                       r2    |10  |  19|        10                    r3    |20  | 100|        20                                                                         

from excel sheet, have field quantity purchased want retrieve range coming table.

e.g

if qty = 6 r1, if qty = 56 r3  

and on

this not difficult vlookup when table static. problem fact number of ranges can change (due macro wrote). here it's 3 ranges, can have more or less ranges different values. can please help?

when you're trying evaluate multiple things can use "and" in vba excel version, both expressions must return true receive true answer.

this should going in right direction,

basic logic

a = b : equals b

a <= b : less or equal b

a >= b : greater or equal b

a < b : less b

a > b : greater b

a <> b : not equal b

sub ranger()  if range("a1") > 1 , range("a1") < 3 msgbox true else msgbox false end if  end sub 

put 2 in cell a1