Help writing formula

[Get_Involved]

I have a spreadsheet table "Federal Tax Rates"

Marginal Tax Brackets
Tax Rate Over But Not Over

row 1,Column A 10.0% Column B $0 Column C $17,850
row 2,Column A 15.0% Column B $17,850 Column C $72,500
row 3,Column A 25.0% Column B $72,500 Column C $146,400
row 4,Column A 28.0% Column B $146,400 Column C $223,050
row 5,Column A 33.0% Column B $223,050 Column C $398,350
row 6,Column A 35.0% Column B $398,350 Column C $450,000
row 7,Column A 39.6% Column B $450,000

I also have a spreadsheet "Payroll"

Column A Column B Column C
Gross Pay Tax Status Federal Income Percent
row 1 $15,000 1 should be 10.0%
row 2 $50,000 1 should be 15.0%

the formula I need is find the column A1 under gross Pay, with column B1 under Tax Status find the percentage from "Federal Tax Rates" and place it in C1 of "Payroll"

There will be four different tables in the "Federal Tax Rates" spreadsheet I can place the numbers 1,2,3, or 4 in (column b Tax Status) of the Payroll spreadsheet


Hope this is understandable.
Thanks for any help.

 

[bschwartz]

Glad to hear that. I was trying to build it for future tables (1,2,3, and 4). When you add that data, you can use the full formula.

 

[Get_Involved]

Ben
This formula works perfect with the ({1,2}

=LOOKUP(Payroll!M5,CHOOSE({1,2},INDEX(('Federal Tax Rates'!$C$15:$C$21,'Federal Tax Rates'!$D$15:$D$21,'Federal Tax Rates'!$G$15:$G$21,'Federal Tax Rates'!$H$15:$H$21),,,Payroll!S5),'Federal Tax Rates'!$B$15:$B$21))

When I add this formula the result is #VALUE

=LOOKUP(Payroll!M5,CHOOSE({1,2,3,4},INDEX(('Federal Tax Rates'!$C$15:$C$21,'Federal Tax Rates'!$D$15:$D$21,'Federal Tax Rates'!$G$15:$G$21,'Federal Tax Rates'!$H$15:$H$21,'Federal Tax Rates'!$K$15:$K$21,'Federal Tax Rates'!$L$15:$L$21,'Federal Tax Rates'!$O$15:$O$21,'Federal Tax Rates'!$P$15:$P$21),,,Payroll!S5),'Federal Tax Rates'!$B$15:$B$21))

The 4 tables is in this formula can you tell where I am going wrong?

 

[bschwartz]

Can you explain in words what you are trying to do? From the way you rearranged the formula, it appears to be looking up 8 tables. Also, the Choose function shouldn't be changed. Only the Index piece impacts how many tables will be referenced.

To add more tables, simply add more ranges to the Index function. Which columns contain the 4 tables?

 

[Get_Involved]

I cant get it look right for when I submit it. and I am not good enough at writing to explain what I want. what I am trying is to make my own personal tax sheet in a workbook to keep track of my truck expenses.
Thanks anyway
Bill

 

[bschwartz]

I can't make heads or tails of that. Try surrounding the cells with borders, and then copying/pasting.

 

[Get_Involved]

You beat me to the edit thanks for you help

 

[bschwartz]

Bill,

I think we're pretty close. If you can just describe which columns have the 4 tables, I believe I can make the necessary modifications.

 

[Get_Involved]

Table 1 B,C,D Table 2 F,G,H Table 3 J,K,L Table 4 N,O,P all in the 'Federal Tax Rates' worksheet

 

[bschwartz]

So I get a better picture, what are the first two rows of data in columns F, G, and H (row 15 and row 16?

 

[remyramsheed]

Helloo

i have some excel data like from b3 to b35 different dates with different month and year. And from g3 to g 35 different amount. What i need is "only" last month's expense in next worksheet. Plz help me....