# Vlookup Function Problem

1. ## Vlookup Function Problem

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

2. ## Re: Vlookup Function Problem

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

"Parker" <Parker@discussions.microsoft.com> wrote in message
news:A0B3F52F-ECA5-4571-84FC-4651A163F09C@microsoft.com...
> 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

3. ## Re: Vlookup Function Problem

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

"JulieD" wrote:

> 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
>
> "Parker" <Parker@discussions.microsoft.com> wrote in message
> news:A0B3F52F-ECA5-4571-84FC-4651A163F09C@microsoft.com...
> > 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

>
>
>

4. ## Re: Vlookup Function Problem

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:

Parker wrote:
> 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
>
>
> "JulieD" wrote:
>
>
>>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
>>
>>"Parker" <Parker@discussions.microsoft.com> wrote in message
>>news:A0B3F52F-ECA5-4571-84FC-4651A163F09C@microsoft.com...
>>
>>>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

>>
>>
>>

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1