How to Get Results From a Tax Table in Excelby Ken Burnside
Excel handles table lookups very cleanly and quickly, and the =VLOOKUP() and =MATCH() functions allow you to move through data tables easily. One common example of how to use =VLOOKUP() and =MATCH() is pulling information out of an IRS tax table. The VLOOKUP() function is used to find the row of the table, and the =MATCH() function is used to determine which column is used. Between them, you get a dynamically readable table lookup.
Enter the tax tables in Excel, or copy and paste them from the PDF file from www.IRS.gov. The IRS usually releases the tax schedules in Excel format in early February of each year.
Open Excel normally. You should see a table with six columns – At Least, But Less than, Single, Married Filing Jointly, Married Filing Separately, and Head of Household, with the requisite tax data under each.
Select cell H1 and click on the Data tab, and select Data Validation. In the dialog box that comes up, select List for the type of validation, and enter the following formula for the source: "=$C$1:$F$1". This will create a drop-down for the filing status types.
Enter your net adjusted income into cell J1.
Enter the following formula in cell K1: "=VLOOKUP(J1,A2:F2063,MATCH(H1,A1:F1,0),FALSE)". This formula takes your income (cell J1) and finds the row that corresponds to it, then matches the filing status (from the drop down in cell H1), to find the exact column, and then returns the data that's at that point.
- For older versions of Excel, to get to the Data Validation dialog box, go to the Data menu, and select Validation.