I need a formula that will search 4 different criteria and return a value
My criteria is "weekly", "married", $2,000. I need a formula that searches for "weekly" in the first column, "married" in the second column, greater than/equal to $2,000 in the third column, and less than $2,000 in the fourth column. Return value in the 5th column.
So with the above criteria the formula should return $199.50.
| weekly | single | $ - | $ 43.00 | $ - | 0.00% | $ - |
| weekly | single | $ 43.00 | $ 222.00 | $ - | 10.00% | $ 43.00 |
| weekly | single | $ 222.00 | $ 767.00 | $ 17.90 | 15.00% | $ 222.00 |
| weekly | single | $ 767.00 | $ 1,796.00 | $ 99.65 | 25.00% | $ 767.00 |
| weekly | single | $ 1,796.00 | $ 3,700.00 | $ 356.90 | 28.00% | $ 1,796.00 |
| weekly | single | $ 3,700.00 | $ 7,992.00 | $ 890.02 | 33.00% | $ 3,700.00 |
| weekly | single | $ 7,992.00 | $ 8,025.00 | $ 2,306.38 | 35.00% | $ 7,992.00 |
| weekly | single | $ 8,025.00 | ∞ | $ 2,317.93 | 39.60% | $ 8,025.00 |
| weekly | married | $ - | $ 164.00 | $ - | 0.00% | $ - |
| weekly | married | $ 164.00 | $ 521.00 | $ - | 10.00% | $ 164.00 |
| weekly | married | $ 521.00 | $ 1,613.00 | $ 35.70 | 15.00% | $ 521.00 |
| weekly | married | $ 1,613.00 | $ 3,086.00 | $ 199.50 | 25.00% | $ 1,613.00 |
| weekly | married | $ 3,086.00 | $ 4,615.00 | $ 567.75 | 28.00% | $ 3,086.00 |
| weekly | married | $ 4,615.00 | $ 8,113.00 | $ 995.87 | 33.00% | $ 4,615.00 |
| weekly | married | $ 8,113.00 | $ 9,144.00 | $ 2,150.21 | 35.00% | $ 8,113.00 |
| weekly | married | $ 9,144.00 | ∞ | $ 2,511.06 | 39.60% | $ 9,144.00 |
I used SUMPRODUCT in Excel. Can't seem to find a work around. Thanks in advance.
Mac mini (Mid 2010), Mac OS X (10.7.5)
