Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup problem (Excel 2000 sr1)

    I have two VLOOKUP tables called SIN04 and SIN05. The range names are determined by taking the entry in row 5 plus the last two digits of the entry in row 4, e.g. SIN04 takes "SIN" in row 5 plus the last 2 digits of the year 2004 in row 4, and SIN05 takes the last two digits of 2005. This part isn't a problem but I include it so you'll know what's going on in the formula below.

    SIN04 29050 0.25 4000

    SIN05 29700 0.25 4090

    I have a formula that takes a number on the spreadsheet, subtracts column 1 of the table, multiplies the result by column 2, and adds column 3.

    Example: =(C30-(VLOOKUP(C30,INDIRECT(C5&RIGHT(C4,2)),1)))*(VLOOKU P(C30,INDIRECT(C5&RIGHT(C4,2)),2))+(VLOOKUP(C30,IN DIRECT(C5&RIGHT(C4,2)),3))

    I'm having a problem with table SIN05. When the number on the spreadsheet (C30 in this case) is higher than column 1 (29700) I get a wildly wrong answer. For instance if C30 is 29701 the formula gives me 41350 when it should give me 4090.75. It's the exact same formula that acts on SIN04 but I don't get a wrong answer from that table (or 39 other similar tables). In this example running it through SIN04 gives me 4200, the correct answer. The number is the result of a formula but the formula, again, is the exact same when being used for SIN04 and SIN05. I substituted a fixed number in C30 in place of the formula result and got the exact same thing so that formula should be OK.

    I've been working on this project for several months and I think I'm getting too close to it to see some problems, so I need help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup problem (Excel 2000 sr1)

    If the numbers are not sorted, you must specify FALSE as 4th argument to VLOOKUP. If you specify TRUE or omit this argument (as in your formulas), Excel expects that the lookup range is sorted in ascending order, and it may return unexpected results otherwise. Try

    =(C30-(VLOOKUP(C30,INDIRECT(C5&RIGHT(C4,2)),1,FALSE)))*( VLOOKUP(C30,INDIRECT(C5&RIGHT(C4,2)),2,FALSE))+(VL OOKUP(C30,INDIRECT(C5&RIGHT(C4,2)),3,FALSE))

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup problem (Excel 2000 sr1)

    Thanks, but they're sorted in ascending order. This program has worked for the past two years, and I'm just adding the 2006 updates to it. But I'm flat out of ideas at this point.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup problem (Excel 2000 sr1)

    I don't understand the example you gave then. Could you post a small demo workbook? Remove or change sensitive data.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup problem (Excel 2000 sr1)

    Hans...
    Here's a snippet from the worksheet. It includes the cells in question, plus both of the VLOOKUP tables. The problem lies in cell C6. You can see the figures for 2004 are OK. I've put 2005 in twice, once with a Taxable Income of 29700 and one with 29701. The tax due for the 29700 is correct, and is only $90 more than the amount due for 2004. But when I add $1 more to the taxable income (29701) the tax jumps by $37260. That doesn't happen in real life. The formulas in B6 and C6 are the exact same.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup problem (Excel 2000 sr1)

    The first column of sin05 is NOT sorted in ascending order. The value 15150 is missing a digit.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup problem (Excel 2000 sr1)

    I told you I needed another set of eyes on this. THANK YOU!

Posting Permissions

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