Results 1 to 7 of 7
Thread: vlookup problem (Excel 2000 sr1)

20051216, 17:14 #1
 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.

20051216, 18:20 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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))

20051216, 18:56 #3
 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.

20051216, 19:15 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20051216, 20:02 #5
 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.

20051216, 20:14 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20051216, 20:17 #7
 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!