+ Reply to Thread
Results 1 to 4 of 4

Vlookup Function Problem

  1. #1
    Parker
    Guest

    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. #2
    JulieD
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Parker
    Guest

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #4
    Debra Dalgleish
    Guest

    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:

    http://groups.google.ca/groups?&selm...%40cppssbbsa04

    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" <[email protected]> wrote in message
    >>news:[email protected]...
    >>
    >>>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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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