Tax Formula

[Robby19]

Thanks to the IRS and how they publish data I am in need of an interesting Index Match or some such formula. The parameters are below;

IRS Table - Single
[TABLE="width: 500"]
<TBODY>[TR]
[TD]At Least
[/TD]
[TD]But Less Than
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]305
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[TD]0.0765
[/TD]
[/TR]
[TR]
[TD]305
[/TD]
[TD]325
[/TD]
[TD]25.1
[/TD]
[TD]24.1
[/TD]
[TD]24.1
[/TD]
[TD]24.1
[/TD]
[TD]24.1
[/TD]
[TD]24.1
[/TD]
[TD]24.1
[/TD]
[TD]24.1
[/TD]
[TD]24.1
[/TD]
[TD]24.1
[/TD]
[TD]24.1
[/TD]
[/TR]
[TR]
[TD]325
[/TD]
[TD]345
[/TD]
[TD]28.63
[/TD]
[TD]25.63
[/TD]
[TD]25.63
[/TD]
[TD]25.63
[/TD]
[TD]25.63
[/TD]
[TD]25.63
[/TD]
[TD]25.63
[/TD]
[TD]25.63
[/TD]
[TD]25.63
[/TD]
[TD]25.63
[/TD]
[TD]25.63
[/TD]
[/TR]
[TR]
[TD]3445
[/TD]
[TD]3485
[/TD]
[TD]628.07
[/TD]
[TD]586.07
[/TD]
[TD]545.07
[/TD]
[TD]503.07
[/TD]
[TD]462.07
[/TD]
[TD]420.07
[/TD]
[TD]379.07
[/TD]
[TD]339.07
[/TD]
[TD]304.07
[/TD]
[TD]269.07
[/TD]
[TD]265.07
[/TD]
[/TR]
</TBODY>[/TABLE]

The first, I would say 100 rows are almost identical when it comes to 1-10, however after that they start changing, as you can see from the above example.

My spreadsheet;
Marital Status (N4) - Dropdown Single, Married
# of Withholdings Claimed (N5) - Dropdown 0-10
Monthly Pay (O5)

The formula should do the following;
IF N4 = Single look at "SingleTax" IF Married look at "MarriedTax" Table (both tables are set up exactly the same)
IF N5 = 0 look at the appropriate table under column 0
IF O5 = 306 match the o column cell of that same row.

Example: Single, 0 withholdings, $306 per month

In this example, the result would be 25.1 since he is Single, 0 withholdings and his monthly pay is greater than 305 and less than 325.

 

[AhoyNC]

This link may help.
https://www.youtube.com/watch?v=WogFiO3aOas