I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am attempting to build this formula around. The second picture attached shows a payroll sheet where the formula would then go. The tax is based off the gross income. So if the gross income is between one of the ranges, it should automatically place the corresponding tax amount. I've tried multiple IF formulas and have had no luck, it's always an error. If someone could give a possible formula for it, or a recommendation for formula type that would be great.

enter image description here

enter image description here


1 Answer 1


Formula for Federal taxes (Cell G5)


Formula for State taxes (Cell J5)



  • the formula is based on a simple VLOOKUP
  • the index is calculated by taking the number of allowances and adding "2".
  • the data is treated as "sorted" which means that it will find the closest match.

Data Layout for Federal Taxes


Data Layout for State Taxes

Note the additional row at the top of the State Taxes . This stops errors on the Employee sheets.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.