Vlookup Function Problem

[Guest]

I'm using Office 2000 Professional and I have a vlookup function that works
in most cases, but not for some. I have a formula for a discount factor and
a formula that rounds that factor to the nearest 0.5%. I then look up that
result in a table but there are six values that it does not find (-17.5%,
-20.5%, -23.5%, -28.5%, -34.5% and -35.0%) Any ideas why it cannot find
these certain values in the lookup range and is there any way to fix this?

To see the problem, put the following info & formulas into a blank worksheet:
Cell A1 - "11111"
Cell A2 - "-500"
Cell A3 - "-450"
Cell B2 - "=A2/A$1"
Cell C3 - "=IF(B2<0,MROUND(ABS(B2),0.005),-MROUND(B2,0.005))"
Cell D2 - "=IF(C2>5%,18.5%,IF(C2<-40%,0, VLOOKUP(C2,I$2:J$92,2,FALSE)))"
Copy cells B2 thru D2 down one line
Fill Down A2 to D3 thru row #102
Cell I2 - "5.0%"
Cell I3 - "4.5%"
Cell J2 - "18.250%"
Cell J3 - "18.125%"
Fill Down I2 to J3 thru row #92

Thanks in advance for any help,

Parker

 

[JulieD]

Hi Parker

i'm a bit confused - firstly as in your example you have
B2, C3, D2
do you mean B2, C2, D2 ???

secondly, when i assume you mean B2, C2, D2, when i fill down to C3, D3 i
get
0.0405 in C3 - which returns a #N/A error - which is expected as this value
is not in column I

have i mis-understood the example or the problem?

Cheers
JulieD

 

[Guest]

Sorry, it is supposed to be B2, C2, D2. Everything else is accurate.

The result in C3 should be 0.04. (Verify the MRound formula, you may have
one too many zeros in the 0.005). One other item of note, you must have the
Analysis ToolPak installed for MRound to work. (Tools, Add-Ins, Analysis
ToolPak).

I did get it to work if I use a similiar MRound function in the lookup table
section (Copy column I to column H and enter the following formula into cell
I2 and drag it down to I92:=IF(H2<0,-MROUND(ABS(H2),0.005),MROUND(H2,0.005)).
This basically guarantees that the value to lookup will match a value in the
lookup section, but I shouldn't have to do that...

Again, Thanks in advance for any help or suggestions.

Parker

 

[Debra Dalgleish]

When you drag the fill handle to create the series, some of the numbers
aren't what you expect. If you increase the number of decimals in the
lookup table, you'll see the differences.

The following post by Rob Bovey may help you solve the problem:

http://groups.google.ca/groups?&selm=#3idwEkLAHA.278@cppssbbsa04