Combination of Validation List and VLOOKUP Problem

[Blake]

I have a simple 2 column list that shows Title Insurance Rates. Left column
gives a 2 number price range. Right column provides the Title Insurance
amount for that home value.

Column 1
$40,001 - $41,000

Column 2
$499.75

These values run from $0 - $30,000 to $399,001 - $400,000 in $1,000
increments.

I first set up a Validation drop down box. This seems to work fine. Next I
did a VLOOKUP on that Validation cell to enter the Title Insurance value for
that amount.

Here's the problem: For low priced homes as in the example above, the
lookup provides $2358.75. (The value should pick up the $499.75 as shown
above.

It seems that when looking up the range of $2358.75 I come up with the
highest value on my list. ($399,001 - $400,000)


If I look up $195,001 - $196,000 the system works fine, returning the proper
amount ($1,333.75).

One further example, if I put in $30,001 - $32,000 the answer comes back
$1908.75. (It should be $434.50.) $1,908.75 should be associated with
$309,001 - $310,000.

The VLOOKUP formula looks like the below:

=VLOOKUP(B8,F4:G374,2)

What am I doing wrong????

 

[Terry Gregg]

I have a simple 2 column list that shows Title Insurance Rates. Left column
gives a 2 number price range. Right column provides the Title Insurance
amount for that home value.

Column 1
$40,001 - $41,000

Column 2
$499.75

These values run from $0 - $30,000 to $399,001 - $400,000 in $1,000
increments.

I first set up a Validation drop down box. This seems to work fine. Next I
did a VLOOKUP on that Validation cell to enter the Title Insurance value for
that amount.

Here's the problem: For low priced homes as in the example above, the
lookup provides $2358.75. (The value should pick up the $499.75 as shown
above.

It seems that when looking up the range of $2358.75 I come up with the
highest value on my list. ($399,001 - $400,000)


If I look up $195,001 - $196,000 the system works fine, returning the proper
amount ($1,333.75).

One further example, if I put in $30,001 - $32,000 the answer comes back
$1908.75. (It should be $434.50.) $1,908.75 should be associated with
$309,001 - $310,000.

The VLOOKUP formula looks like the below:

=VLOOKUP(B8,F4:G374,2)

What am I doing wrong????

It could be a couple of things
1/ Try adding ,false after the ,2 in the formula
2/ If you are copying the formula down make sure the range is
$F$4:$G$374 otherwise the range will change as you copy down and give
you strange results.
Terry

 

[pdberger]

Blake --
I think the problem is in your column 1. I think if you set each figure at
the highest number of that particular range, the VLOOKUP function should work
fine. The way it works is that it steps down the index column of the range
until it finds the first number that is higher than the number you're
comparing to. It moves back up one row, and counts out the correct number of
columns. (If you specify "FALSE" at the end, then it looks for a specific
match.) So set up column one to be:

$0
$999.99
$1999.99
$2999.99

and it should work fine. Didn't test it, but I've done similar things and
it worked like that.

HTH

 

[Blake]

Hey thanks a lot.

Least intrusive fix first - I added FALSE to the end of my VLOOKUP and voila
it worked.