Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combination of Validation List and VLOOKUP Problem
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???? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combination of Validation List and VLOOKUP Problem
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???? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combination of Validation List and VLOOKUP Problem
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???? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Cell Validation List & Linked Cell VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup problem.. | Excel Worksheet Functions | |||
Complex Vlookup and List Validation and Nested IF statements | Excel Worksheet Functions | |||
update data validation list with new entries?? | Excel Discussion (Misc queries) | |||
Validation List and VLookup are ackting strange | Excel Worksheet Functions |