Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    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 if-then 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!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  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 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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  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 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....

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  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 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.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    2 Star Lounger
    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.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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