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????

[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" wrote:

> 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????
>
>
>
>
>
>

[Blake]

Hey thanks a lot.

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


"pdberger" wrote:

> 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" wrote:
>
> > 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????
> >
> >
> >
> >
> >
> >