Index() + Match() alternative

ajetrumpet

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
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).
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
You can't use INDEX + MATCH because taxable income doesn't exactly match any value in Taxable income ceiling (usually).

I have 2 sheets:
Overview holds:
- Column A - Status
- Column B - Ceiling
- Column C - Tax rate
- Column D - Mandatory

Data holds:
- A2 = Status (lookup value)
- B2 = Taxable income
- C2 holds:
=VLOOKUP(B2;IF(DAta!$A$2:$A$29=Overview!A2;DAta!$B$2:$C$29);2;1)

D2 holds:
=VLOOKUP(B2;IF(DAta!$A$2:$A$29=Overview!A2;DAta!$B$2:$D$29);3;1)

Obv use Ctrl-Shift-Enter on both.

Good luck, hope this helps.
 
Upvote 0
Excel Workbook
ABCD
1FilingStatusTaxable Income CeilingTax RateMandatory Tax
2100%0
31837510%0
413400015%837.5
518240025%4681.25
6117185028%16781.25
7137365033%41827.25
81100000035%108421.25
9
10Filing StatusTaxable Income CeilingTax RateMandatory Tax
11200%0
1221675010%0
1326800015%1675
14213730025%9362.5
15220925028%26687.5
16237365033%46833.5
172100000035%101085.5
18
19Filing StatusTaxable Income CeilingTax RateMandatory Tax
20300%0
213837510%0
2233400015%837.5
2336865025%4681.25
24310462528%13343.75
25318682533%23416.75
263100000035%50542.75
27
28Filing StatusTaxable Income CeilingTax RateMandatory Tax
29400%0
3041195010%0
Taxes
Excel Workbook
ABCD
1FilingStatusTaxable IncomeTax RateMandatory Tax
2330000033%23416.75
Sheet2
Cell Formulas
RangeFormula
C2=LOOKUP(B2,OFFSET(Taxes!B2:B8,(A2-1)*10,),OFFSET(Taxes!C2:C8,(A2-1)*10,))
D2=LOOKUP(B2,OFFSET(Taxes!B2:B8,(A2-1)*10,),OFFSET(Taxes!D2:D8,(A2-1)*10,))
 
Upvote 0
Alpha,

Thank you so much! Honestly, I have no idea how that works, but it's just fine. One problem with it is the fact that Filing Status 4 gives me an "N/A" in the cell.

Can you give a hint on that as well?

Again, thanks a ton from an Access user (not excel, obviously!). :biggrin:
 
Upvote 0
I'm glad it works. Filing status 4 works fine for me so I'm not sure what to tell you.

The LOOKUP function is fairly simple. If your LOOKUP formula was just this...
=LOOKUP(B2, Taxes!B2:B8, Taxes!C2:C8)

This would lookup the income from B2 in the range Taxes!B2:B8 and return the value from Taxes!C2:C8 from the same row as the column B match. The match of B2 will be the row that is the largest value that is less than or equal to the supplied 'lookup' value (B2) in Taxes!B2:B8

With this expanded formula
=LOOKUP(B2,OFFSET(Taxes!B2:B8,(A2-1)*10,),OFFSET(Taxes!C2:C8,(A2-1)*10,))

The offset function will offset Taxes!B2:B8 by 10 rows for each filing status -1
So Filing status 3(-1) will offset from Taxes!B2:B8 by 20 rows to do the lookup
 
Upvote 0
yeah, I'd have to study that. I tried, for experiment only, in the actual formula, to change this:
Code: 
=LOOKUP($L$3,OFFSET(LookupData!$B$4:$B$10,([COLOR="Red"][B]Overview!$A$5-1[/B][/COLOR])*10,),

OFFSET(LookupData!$D$4:$D$10,([B][COLOR="red"]Overview!$A$5-1[/COLOR][/B])*10,))

to this:
Code: 
=LOOKUP($L$3,OFFSET(LookupData!$B$4:$B$10,([COLOR="red"][B]Overview!$A$5[/B][/COLOR])*10,),

OFFSET(LookupData!$D$4:$D$10,([COLOR="red"][B]Overview!$A$5[/B][/COLOR])*10,))

That gave me an "N/A" for every status. Nothing worked.

Does that help you help me?
 
Upvote 0
The problem with AlphaFrog's original formula is the multiple of 10 in the OFFSET. The sections of data are only 9 rows apart, not 10. If you try say status 4 with a low taxable income (eg 100) you'll see the error.

Also, OFFSET is a volatile function so I generally avoid it if possible.

dafan said:
You can't use INDEX + MATCH because taxable income doesn't exactly match any value in Taxable income ...
Click to expand...
Not so. Since the values are sorted, you can use the final argument of MATCH as 1, just as you did with your VLOOKUP.

Here's a way (done on one sheet but you can adapt to two) using INDEX/MATCH and without OFFSET, though I have used some helper cells. The helper cells would also allow for the table sizes to be different. If they are always the same size as in your example, then the formula in N2 could be simplified to =M3+7

Excel Workbook
ABCDEFGHIJKLMN
1FilingStatusTaxable Income CeilingTax RateMandatory TaxFilingStatusTaxable IncomeTax RateMandatory TaxStart RowEnd Row
2100%0330000033%23416.751926
31837510%0
413400015%837.5
518240025%4681.25
6117185028%16781.25
7137365033%41827.25
81100000035%108421.25
9
10Filing StatusTaxable Income CeilingTax RateMandatory Tax
11200%0
1221675010%0
1326800015%1675
14213730025%9362.5
15220925028%26687.5
16237365033%46833.5
172100000035%101085.5
18
19Filing StatusTaxable Income CeilingTax RateMandatory Tax
20300%0
213837510%0
2233400015%837.5
2336865025%4681.25
24310462528%13343.75
25318682533%23416.75
263100000035%50542.75
27
28Filing StatusTaxable Income CeilingTax RateMandatory Tax
29400%0
3041195010%0
3144555015%1195
32411765025%6235
33419055028%24260
34437365033%44672
354100000035%105095
Taxes
 
Upvote 0
By adding another helper cell, the Tax Rate and Mandatory Tax formulas could be even simpler.

Excel Workbook
HIJKLMNO
1FilingStatusTaxable IncomeTax RateMandatory TaxStart RowEnd RowMy Row
2330000033%23416.75192625
Taxes (2)
 
Upvote 0
Peter_SS is correct. There are only 9 rows between each Status range. So if the Volatile functionality of Offset is not an issue...

Code: 
=LOOKUP($L$3,OFFSET(LookupData!$B$4:$B$10,(Overview!$A$5-1)*[COLOR="Red"]9[/COLOR],),
OFFSET(LookupData!$D$4:$D$10,(Overview!$A$5-1)*[COLOR="Red"]9[/COLOR],))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Multiple answer for particular timesets and making a box if all relevant data matches
Replies
7
Views
176
myall_blues
myall_blues
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
227
kvsrinivasamurthy
K
Dan Wilson
  • Solved
Formula help.
2
Replies
10
Views
239
Jeffrey Mahoney
Jeffrey Mahoney
Joneye
  • Question Question
Index & Match or Other Look up Advice
Replies
2
Views
66
Joneye
Joneye
M
  • Solved
Alternate to nested IFs, comparing values in between percentages
Replies
3
Views
178
Fluff
Fluff

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top