# How to make formula based off a withholding table?

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.  • Hi, welcome to WebApps. The good news is that this is very easily done (though I couldn't quickly locate a precedent). Would you please edit your question to show the values should be displayed for a successful outcome. For those of us not familiar with US?? taxes, please explain what the function of the "Allowances" columns, and how they impact on the result. Jun 17 at 2:29
• @Tedinoz link Here is the link to a sample document. Both Federal and State withholdings are determined by the tables in the document. Each employee is also filing with a certain number of "allowances" shown on their sheets. This determines the column used for their withholdings. They are based off of gross pay. So say someone has a gross pay of \$200 and files 2 allowances, their withholdings would be whatever is in the corresponding column and row of gross
– John
Jun 22 at 1:44

Formula for Federal taxes (Cell G5)

`=ArrayFormula(if(B5:B="","",vlookup(F5:F,FEDL_WH!\$A\$4:\$D\$54,\$B\$2+2,1)))`

Formula for State taxes (Cell J5)

`=ArrayFormula(if(B5:B="","",vlookup(F5:F,STATE_WH!\$A\$4:\$D\$55,\$B\$2+2,1)))`

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