How to Calculate Federal Income Tax in Excel With VLOOKUP

By Ken Burnside

i tax forms image by Chad McDermott from Fotolia.com

If you can get the Internal Revenue Service income tax tables in a format that Microsoft Excel can read (see Resources for one source), you can use the VLOOKUP and MATCH functions of the software to pull data out of those tables quickly and easily. VLOOKUP is a table search function that scans down the first column until it finds a matching item and then cross-references to a column to the right in the table to find the corresponding value. You can use MATCH to determine the column of the table to be searched by matching text entered against existing column headers..

Step 1

Import the tax tables into Excel, by going to "File" and then "Open."

Step 2

Select the column headers for the tax table. Click on the Formula tab, and select "Name Manager," and give that range the name TaxTableHeaders. Click "OK" to save this named range.

Step 3

Select the entire tax table, from top to bottom, and click on the "Name Manager" icon and give it the name "TaxTable"

Step 4

Select cell K1. Select the "Data" tab, and click on the "Data Validation" button. Select "List" from the type of validations and enter "=TaxTableHeaders" for the source. This will turn cell K1 into a drop-down menu, and the drop-down menu list will be the headers of the tax table.

Step 5

Enter your annual income into cell K2.

Step 6

Enter the following formula in cell K3: "=VLOOKUP(K2,TaxTable,MATCH(K1,TaxTableHeaders,0),TRUE)"

Step 7

Select the number of dependents you have in cell K1, and cell K3 will show your total tax due.

References

Tips

Writer Bio