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.

 

[Get_Involved]

F row 15 [10.0%] G row 15 [$0 ] H row 15 [$8,925]
F row 16 [15.0%] F row 16 [$8,925] H row 16 [$36,250]

G is the lowest dollar H is the highest dollar any dollars in between are charged the percent in f row

 

[bschwartz]

Try this (assuming the rates are the same for all tables, 10%,15,25,28,33,35,39):

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

 

[Get_Involved]

That is getting real close. I am a truck driver an got to get in bed just a little more tweaking and you got it. thanks
Later

 

[Get_Involved]

Hello

the formula is working some what , but the Tax status 1,2,3, and 4, in Payroll S5 is not pulling from the right tables in the Worksheet "Federal Tax Rates"
Tax Rate percent in column B can be used with all the tables
Tax status 1 is table Named "Married Filing Jointly" column C and D
Tax status 2 is table Named "Married Filing Separately" column G and H
Tax status 3 is table Named "Single" column K and L
Tax status 4 is table Named "Head of Household" column O and P

The dollar over but not over are different in each table.
hope this makes since.

 

[bschwartz]

It's working for me with the following structure. Note that I have hidden some columns, however, I am trying to replicate your setup (so please let me know if something is not right):

I invented some tables, since I don't have all of your numbers. I am also only showing the low side columns since that's all I need for the formula:

Excel 2010
	B	C	G	K	O
14	Rate	Married Filing Jointly	Married Filing Separately	Single	Head of Household
15	10.00%	0	0	0	0
16	15.00%	17,850	20,000	30,000	72,500
17	25.00%	72,500	90,000	70,000	146,400
18	28.00%	146,400	150,000	110,000	223,050
19	33.00%	223,050	250,000	300,000	398,350
20	35.00%	398,350	410,000	375,000	420,000
21	39.60%	450,000	450,000	450,000	450,000
Federal Tax Rates

Excel 2010
	M	S	V
4	Gross Pay	Tax Status	Federal Income Percent
5	15,000	2	10.0%
6	50,000	4	10.0%
7	75,000	3	25.0%
8	15,000	1	10.0%
9	200,000	3	28.0%
10	350,000	1	33.0%
11	400,000	2	33.0%
12	60,000	1	15.0%
13	80,000	3	25.0%
14	120,000	2	25.0%
15	250,000	1	33.0%
16	310,000	2	33.0%
Payroll

Cell Formulas
Range	Formula
V5	=LOOKUP(Payroll!M5,CHOOSE({1,2},INDEX(('Federal Tax Rates'!$C$15:$C$21,'Federal Tax Rates'!$G$15:$G$21,'Federal Tax Rates'!$K$15:$K$21,'Federal Tax Rates'!$O$15:$O$21),,,Payroll!S5),'Federal Tax Rates'!$B$15:$B$21))

 

[Get_Involved]

Problem solved!
Ben You are the best it works perfect thank you, sorry I could explain it better the first time.

 

[bschwartz]

I'm glad we got it to work in the end. Thanks for the feedback.