Vlookup rounding up rather than down

[Steve]

I'm trying to make a chart of State mandated insurance rates. One
column is policy amounts in $500 increments: 10,000, 10,500, 11,000
etc. Another column is rates for each policy amount: 229, 233, 235
(no set pattern), I set it in Vlookup; VLOOKUP(PolicyAmt,Rate,
2,TRUE). I said TRUE because I need the amount for everything in
between the charted increments, e.g, 10,200.55. It doesn't work. The
rates are _up to and including_ the set amounts, so 10,200.55 (or
whatever) coverage should have a 233 premium. Vlookup returns the
lower amount, so 10,250.55 coverage shows just 229. Excel Help says
Vlookup looks for the exact value in the left column or the next
_lower_ value. Is there any way to round up instead of down? Am I
trying to make water flow uphill? Thanks.

 

[Guest]

Try table as:

99999 240 <=== your maximum
11000 235
10500 233
10000 229

and use formula:

=INDEX(B1:B4,MATCH(D1,A1:A4,-1))

where D1 is your lookup value

HTH

 

[Ron Rosenfeld]

I'm trying to make a chart of State mandated insurance rates. One
column is policy amounts in $500 increments: 10,000, 10,500, 11,000
etc. Another column is rates for each policy amount: 229, 233, 235
(no set pattern), I set it in Vlookup; VLOOKUP(PolicyAmt,Rate,
2,TRUE). I said TRUE because I need the amount for everything in
between the charted increments, e.g, 10,200.55. It doesn't work. The
rates are _up to and including_ the set amounts, so 10,200.55 (or
whatever) coverage should have a 233 premium. Vlookup returns the
lower amount, so 10,250.55 coverage shows just 229. Excel Help says
Vlookup looks for the exact value in the left column or the next
_lower_ value. Is there any way to round up instead of down? Am I
trying to make water flow uphill? Thanks.

Offset your premiums by one row, so they "line up" in accord with the VLOOKUP
requirements.

=VLOOKUP(10200.55,{9500,229;10000,233;10500,235;11000,242},2)

You may have to change the values a bit:

=VLOOKUP(10200.55,{9499.99,229;9999.99,233;10499.99,235;10999.99,242},2)
--ron

 

[Steve]

Thanks for the input. Maybe I didn't fully set out the issue. The
State sets rates from zero to 100,000. Premiums are 229 up to and
including 10,000; 233 up to and including 10,500, and so on to 833 for
up to and including 100,000. This means I have 180 entries in the
column of policy amounts and the same number of premium amounts. I
tried to set up the formula for policy amounts exactly matching the
180 breaks set by the state and everything else in between. If my
formula had to deal with multiples of 500 and everything else, there
would be ten million iterations. That's what I'm trying to avoid.

 

[Ron Rosenfeld]

Thanks for the input. Maybe I didn't fully set out the issue. The
State sets rates from zero to 100,000. Premiums are 229 up to and
including 10,000; 233 up to and including 10,500, and so on to 833 for
up to and including 100,000. This means I have 180 entries in the
column of policy amounts and the same number of premium amounts. I
tried to set up the formula for policy amounts exactly matching the
180 breaks set by the state and everything else in between. If my
formula had to deal with multiples of 500 and everything else, there
would be ten million iterations. That's what I'm trying to avoid.

You either didn't try or didn't understand my response.

If the first "bracket" is from 0-10000, then merely set the first entry to 0
instead of the 9500.

Since you wrote that the premiums did not occur in any particular pattern, you
will have to have a separate entry in your table for each bracket. I don't
know of any way around that if there is no mathematical relationship between
the amount and the premium.

So you still just need to offset your table as I wrote.

For example:

$ 0.00 229
$10,000.01 233
$10,500.01 235

=VLOOKUP(amt,tbl,2)

where amt is the amount you are checking; tbl is the two column table set up as
above and extended through to $100,000
--ron