Results 1 to 10 of 10

20040523, 19:15 #1
 Join Date
 Apr 2004
 Location
 Jacksonville, Florida, USA
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
Vlookup again...aargh! (Excel 97)
I have 4 schedules per year, with 6 possible formulas per schedule, depending on an amount. As an example, if the amount on Schedule#2 is between $15k and $25k we use one formula, but if the amount is between $25k and $35k we use another formula. I'm using 2 Vlookup tables in an ifthen statement, one that gives me the schedule number, and a second that gives me the formula, but this is not working out at all. Here's what I've got so far:
=IF(VLOOKUP($C$17,range2,2,FALSE)=2,(((C23(VLOOKUP($C$23,test2,1)))*(VLOOKUP($C$23,test2,2)) ))+(VLOOKUP($C$23,test2,3)),((C23(VLOOKUP($C$23,test3,1)))*(VLOOKUP($C$23,test3,2)) )+(VLOOKUP($C$23,test3,3)))
I haven't programmed in Excel in a while, but you guys helped me through an earlier part of this project, so here I am again. The client doesn't want macros, only formulas. And I am dead in the water at this point. HELP!!!

20040523, 19:38 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Vlookup again...aargh! (Excel 97)
This is rather hard to visualize. Could you post a stripped down copy of the workbook, with some indication of what you want to accomplish? Replace sensitive data with dummy data, if necessary.

20040523, 20:24 #3
 Join Date
 Apr 2004
 Location
 Jacksonville, Florida, USA
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Vlookup again...aargh! (Excel 97)
I can't actually put the program on the forum, but what I'm trying to do is compute US income tax. A person can file in any one of 4 statuses  Single, Married filing jointly, Married filing separately, and Head of Household. These are my 4 schedules.
On each of these schedules the tax is computed as follows: if the taxable income is in this bracket, the tax is this amount plus this percentage of the amount over another amount. For instance, in 2003 if you filed as a single person and your income was $50k, your income would be in the $28400 to $68800 bracket, and the tax would be $3910 plus 25% of the amount over $28400. There are 6 of these brackets for each filing status.
My program lists income, withholding tax, filing status etc., and comes down to a bottom line of taxable income. I need to compute the tax on this and have it appear in the next cell down. Note that this program is not designed for individuals to use, therefore we aren't striving for deadly accuracy. As long as we're in the ballpark on the amount of tax due that's fine. I have a Vlookup table that can take care of the 6 brackets for one filing status for one year. Problem is, we're working with the last 17 years, so multiply that by 4 filing statuses per year and I need the program to decide which of those 68 Vlookup tables to access.
And that's where I get in over my depth! Thanks in advance for your help.

20040523, 20:35 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Vlookup again...aargh! (Excel 97)
I'll leave this for a US citizen to solve <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

20040523, 21:11 #5
 Join Date
 Apr 2004
 Location
 Jacksonville, Florida, USA
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Vlookup again...aargh! (Excel 97)
Geez, it's just a matter of telling the program which of 68 items to look up, based on 2 variables. You don't have to be a US citizen to figure that out....

20040523, 21:21 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Vlookup again...aargh! (Excel 97)
I'd want to have a spreadsheet to work on <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

20040523, 22:03 #7
 Join Date
 Apr 2004
 Location
 Jacksonville, Florida, USA
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Vlookup again...aargh! (Excel 97)
Here's the sanitized version of the spreadsheet. All of the lookup tables are on sheet 2. The cell I'm trying to get working is Tax Due C24.

20040523, 22:32 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Vlookup again...aargh! (Excel 97)
You already have names for each of your tables with the year and status/year (Sin03, MFJ03, etc)
Then do a vlookup using INDIRECT to get the table name
=(C23(VLOOKUP(C23,INDIRECT(C17&RIGHT(C4,2)),1))) * (VLOOKUP($C$23,INDIRECT(C17&RIGHT(C4,2)),2)) + (VLOOKUP($C$23,INDIRECT(C17&RIGHT(C4,2)),3))
You will have to change the entry into row 17 to be "Sin" instead of "S" since that is what you used for the name.
Steve

20040523, 23:33 #9
 Join Date
 Apr 2004
 Location
 Jacksonville, Florida, USA
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Vlookup again...aargh! (Excel 97)
Thank you! It works perfectly. Now I just have to figure out HOW it works! I'm unfamiliar with the Indirect function and am reading up on it now.

20040524, 08:41 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Vlookup again...aargh! (Excel 97)
The formula grabs the value from C17 (the status) and the right 2 characters from the year. Together they make up the name of the table you want to look up (ie after using "Sin" instead of "S" for status). you want to "indirectly" use this name for the lookup table, so you use the indirect function. (direct entry is what you had, entering "Sin03" into the cell). Indirect allows it to change based on the other cell values
Steve