Index() + Match() alternative

[ajetrumpet]

hello all,

I've been looking here: http://support.microsoft.com/kb/59482 , but I don't think that's what I need. I have a tax table that looks like this:<br /><br /><table cellspacing="0" cellpadding="0"><col width="173" /><col width="214" /><col width="89" /><col width="141" /><tr height="20"><td height="20" width="173">FilingStatus</td><td width="214">Taxable Income Ceiling</td><td width="89">Tax Rate</td><td width="141">Mandatory Tax</td></tr><tr height="20"><td height="20" align="right">1</td><td align="right">0</td><td align="right">0%</td><td align="right">0</td></tr><tr height="20"><td height="20" align="right">1</td><td align="right">8375</td>
<td align="right">10%</td><td align="right">0</td></tr><tr height="20"><td height="20" align="right">1</td><td align="right">34000</td><td align="right">15%</td><td align="right">837.5</td></tr><tr height="20"><td height="20" align="right">1</td><td align="right">82400</td><td align="right">25%</td><td align="right">4681.25</td></tr><tr height="20"><td height="20" align="right">1</td><td align="right">171850</td><td align="right">28%</td><td align="right">16781.25</td></tr><tr height="20"><td height="20" align="right">1</td><td align="right">373650</td><td align="right">33%</td><td align="right">41827.25</td></tr><tr height="20"><td height="20" align="right">1</td><td align="right">1000000</td><td align="right">35%</td><td align="right">108421.25</td></tr><tr height="20"><td height="20"></td><td></td><td></td><td></td></tr><tr height="20"><td height="20">Filing Status</td><td>Taxable Income Ceiling</td><td>Tax Rate</td><td>Mandatory Tax</td></tr><tr height="20"><td height="20" align="right">2</td><td align="right">0</td><td align="right">0%</td><td align="right">0</td></tr><tr height="20"><td height="20" align="right">2</td><td align="right">16750</td><td align="right">10%</td><td align="right">0</td></tr><tr height="20"><td height="20" align="right">2</td><td align="right">68000</td><td align="right">15%</td><td align="right">1675</td></tr><tr height="20"><td height="20" align="right">2</td><td align="right">137300</td><td align="right">25%</td><td align="right">9362.5</td></tr><tr height="20"><td height="20" align="right">2</td><td align="right">209250</td><td align="right">28%</td><td align="right">26687.5</td></tr><tr height="20"><td height="20" align="right">2</td><td align="right">373650</td><td align="right">33%</td><td align="right">46833.5</td></tr><tr height="20"><td height="20" align="right">2</td><td align="right">1000000</td><td align="right">35%</td><td align="right">101085.5</td></tr><tr height="20"><td height="20"></td><td></td><td></td><td></td></tr><tr height="20"><td height="20">Filing Status</td><td>Taxable Income Ceiling</td><td>Tax Rate</td><td>Mandatory Tax</td></tr><tr height="20"><td height="20" align="right">3</td><td align="right">0</td><td align="right">0%</td><td align="right">0</td></tr><tr height="20"><td height="20" align="right">3</td><td align="right">8375</td><td align="right">10%</td><td align="right">0</td></tr><tr height="20"><td height="20" align="right">3</td><td align="right">34000</td><td align="right">15%</td><td align="right">837.5</td></tr><tr height="20"><td height="20" align="right">3</td><td align="right">68650</td><td align="right">25%</td><td align="right">4681.25</td></tr><tr height="20"><td height="20" align="right">3</td><td align="right">104625</td><td align="right">28%</td><td align="right">13343.75</td></tr><tr height="20"><td height="20" align="right">3</td><td align="right">186825</td><td align="right">33%</td><td align="right">23416.75</td></tr><tr height="20"><td height="20" align="right">3</td><td align="right">1000000</td><td align="right">35%</td><td align="right">50542.75</td></tr><tr height="20"><td height="20"></td><td></td><td></td><td></td></tr><tr height="20"><td height="20">Filing Status</td><td>Taxable Income Ceiling</td><td>Tax Rate</td><td>Mandatory Tax</td></tr><tr height="20"><td height="20" align="right">4</td><td align="right">0</td><td align="right">0%</td><td align="right">0</td></tr><tr height="20"><td height="20" align="right">4</td><td align="right">11950</td><td align="right">10%</td><td align="right">0</td></tr><tr height="20"><td height="20" align="right">4</td><td align="right">45550</td><td align="right">15%</td><td align="right">1195</td></tr><tr height="20"><td height="20" align="right">4</td><td align="right">117650</td><td align="right">25%</td><td align="right">6235</td></tr><tr height="20"><td height="20" align="right">4</td><td align="right">190550</td><td align="right">28%</td><td align="right">24260</td></tr><tr height="20"><td height="20" align="right">4</td><td align="right">373650</td><td align="right">33%</td><td align="right">44672</td></tr><tr height="20"><td height="20" align="right">4</td><td align="right">1000000</td><td align="right">35%</td><td align="right">105095</td></tr></table>

This table is on one tab, which is being used for calculations on an 'overview' tab. I need to write a formula in one of the overview cells that will do this:

*Take 2 cell inputs (filing status number and taxable income) and use it to calculate, from the above table:

1. Mandatory Tax + another calculation using the appropriate Rate and Ceiling as factors, based on the input cell values (I know the formula, but referencing the rates and ceilings is what I'm stuck on).

Man, I'll tell ya, I've tried for a while now and I can't seem to figure out how to do it. Can someone give me a push here? Thanks!

(if you do answer this, please do not mention tax software. I'm an accountant and I'm putting this tax estimator together for people that don't have the knowledge to understand how taxes work).

 

[ukmikeb]

Hi

Or this variation on Peters first offering but without helper columns -

In I2 -

=INDEX(($C$2:$C$8,$C$11:$C$17,$C$20:$C$26,$C$29:$C$35),MATCH($I2,INDEX(($B$2:$B$8,$B$11:$B$17,$B$20:$B$26,$B$29:$B$35),,,$H$2),1),,$H$2)

and in J2 -

=INDEX(($D$2:$D$8,$D$11:$D$17,$D$20:$D$26,$D$29:$D$35),MATCH($I2,INDEX(($B$2:$B$8,$B$11:$B$17,$B$20:$B$26,$B$29:$B$35),,,$H$2),1),1,$H$2)

hth

 

[ajetrumpet]

Thanks guys! Wow, what help!

hey Peter, I got one small problem. I think the question may have been read wrong or something. It's not a big deal, but since I'm copying and pasting here, I can't do it myself.

With your formulas, and Alpha's, all of the references are looking up values in the tax table (regardless of column) that are one value too small. For instance...

In your example, you used $300,000 for income and the return values were all on row 25, when they should come from row 26. Does that make sense?

I need return values from the row that has the first ceiling > (greater than) the income. So for filing status 3 and income of 300K, the values need to come from row 26 (ceiling of 10 million and tax rate of 35%).

I hope it's a short fix? Thanks again guys, such great help here.

 

[ajetrumpet]

Nevermind guys. It was fixed hours ago, just forgot to update this thread.

Everyone here was extremely helpful. Thanks a bunch!