# Thread: Vlookup again...aargh! (Excel 97)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•