Help writing formula

G

Get_Involved

Active Member
Joined
Jan 26, 2007
Messages
383
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
How about this?

=LOOKUP(Payroll!A2,CHOOSE({1,2},INDEX(('Federal Tax Rates'!$B$2:$B$8,'Federal Tax Rates'!$C$2:$C$8,'Federal Tax Rates'!$D$2:$D$8,'Federal Tax Rates'!$E$2:$E$8),,,Payroll!B2),'Federal Tax Rates'!$A$2:$A$8))
 
Upvote 0
Thanks for the response Ben
That formula would only give "#N/A when I put my cells into it.
So I reworked the question to my actual cells if you don't mind me doing so.

I have a spreadsheet table "Federal Tax Rates"

Marginal Tax Brackets
Tax Rate Over But Not Over

Column B Column C Column D

row 14, 10.0% $0 $17,850
row 15 15.0% $17,850 $72,500
row 16 25.0% $72,500 $146,400
row 17 28.0% $146,400 $223,050
row 18 33.0% $223,050 $398,350
row 19 35.0% $398,350 $450,000
row 20 39.6% $450,000

I also have a spreadsheet "Payroll"

Column M Column S Column V
Gross Pay Tax Status Federal Income Percent
row 5 $15,000 1 should be 10.0%


the formula I need is find what is in column M5 under gross Pay, and what is in column S5 under Tax Status in the Payroll worksheet, now find the percentage from "Federal Tax Rates" in columns B14:B20 and place it in column V5 of Payroll worksheet.
Hope that better
Thanks
PS there will be four different tables in spreadsheet "Federal Tax Rates"
 
Last edited:
Upvote 0
"Federal Tax Rates"
I have presented just the first set Table 1 Tax Status 1. columns B,C, and D. I will add the other table formulas after I get this one down pat. the other Tables and Tax Status will just be (2,3,4) in different columns
Thank you for your response
 
Upvote 0
Can you explain the function of column D? It seems to be the same earning numbers as column B but without the 0. Which is the lookup supposed to search for, column C or column D?
 
Upvote 0
They are not I had the column B,C, and C spread and centered over each set of numbers, when I summited they thread it moved together.
B is the percent of tax to pay, C is the low side, and D is the high side I need the what is between is the percent in B.
 
Last edited:
Upvote 0
I'm still not sure I understand, but please try this formula in cell V5 and let me if it works:

=LOOKUP(Payroll!M5,CHOOSE({1,2},INDEX(('Federal Tax Rates'!$C$14:$C$20,'Federal Tax Rates'!$D$14:$D$20,'Federal Tax Rates'!$E$14:$E$20,'Federal Tax Rates'!$F$14:$F$20),,,Payroll!S5),'Federal Tax Rates'!$B$14:$B$20))
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calculating income tax in reverse
Replies
9
Views
240
Dave Patton
D
Chris_010101
  • Question
UK Tax Brackets - Formulas
Replies
1
Views
2K
etaf
etaf
V
  • Solved
Formula Help
Replies
1
Views
265
Peter_SSs
Peter_SSs
Chris_010101
  • Solved
SUMIF/SUMIFS Help
Replies
8
Views
425
Fluff
Fluff
S
  • Question
Formula help with removing duplicates while looking at other column criteria
Replies
5
Views
205
samide2001
S

Forum statistics

Threads
1,214,950
Messages
6,122,436
Members
449,083
Latest member
Ava19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top