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).

 

[dafan]

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.

 

[AlphaFrog]

Excel Workbook
	A	B	C	D
1	FilingStatus	Taxable Income Ceiling	Tax Rate	Mandatory Tax
2	1	0	0%	0
3	1	8375	10%	0
4	1	34000	15%	837.5
5	1	82400	25%	4681.25
6	1	171850	28%	16781.25
7	1	373650	33%	41827.25
8	1	1000000	35%	108421.25
9
10	Filing Status	Taxable Income Ceiling	Tax Rate	Mandatory Tax
11	2	0	0%	0
12	2	16750	10%	0
13	2	68000	15%	1675
14	2	137300	25%	9362.5
15	2	209250	28%	26687.5
16	2	373650	33%	46833.5
17	2	1000000	35%	101085.5
18
19	Filing Status	Taxable Income Ceiling	Tax Rate	Mandatory Tax
20	3	0	0%	0
21	3	8375	10%	0
22	3	34000	15%	837.5
23	3	68650	25%	4681.25
24	3	104625	28%	13343.75
25	3	186825	33%	23416.75
26	3	1000000	35%	50542.75
27
28	Filing Status	Taxable Income Ceiling	Tax Rate	Mandatory Tax
29	4	0	0%	0
30	4	11950	10%	0
Taxes

Excel Workbook
	A	B	C	D
1	FilingStatus	Taxable Income	Tax Rate	Mandatory Tax
2	3	300000	33%	23416.75
Sheet2

Cell Formulas
Range	Formula
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,))

 

[ajetrumpet]

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!).

 

[AlphaFrog]

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

 

[ajetrumpet]

yeah, I'd have to study that. I tried, for experiment only, in the actual formula, to change this:

=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:

=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?

 

[Peter_SSs]

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.

You can't use INDEX + MATCH because taxable income doesn't exactly match any value in Taxable income ...

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	FilingStatus	Taxable Income Ceiling	Tax Rate	Mandatory Tax				FilingStatus	Taxable Income	Tax Rate	Mandatory Tax		Start Row	End Row
2	1	0	0%	0				3	300000	33%	23416.75		19	26
3	1	8375	10%	0
4	1	34000	15%	837.5
5	1	82400	25%	4681.25
6	1	171850	28%	16781.25
7	1	373650	33%	41827.25
8	1	1000000	35%	108421.25
9
10	Filing Status	Taxable Income Ceiling	Tax Rate	Mandatory Tax
11	2	0	0%	0
12	2	16750	10%	0
13	2	68000	15%	1675
14	2	137300	25%	9362.5
15	2	209250	28%	26687.5
16	2	373650	33%	46833.5
17	2	1000000	35%	101085.5
18
19	Filing Status	Taxable Income Ceiling	Tax Rate	Mandatory Tax
20	3	0	0%	0
21	3	8375	10%	0
22	3	34000	15%	837.5
23	3	68650	25%	4681.25
24	3	104625	28%	13343.75
25	3	186825	33%	23416.75
26	3	1000000	35%	50542.75
27
28	Filing Status	Taxable Income Ceiling	Tax Rate	Mandatory Tax
29	4	0	0%	0
30	4	11950	10%	0
31	4	45550	15%	1195
32	4	117650	25%	6235
33	4	190550	28%	24260
34	4	373650	33%	44672
35	4	1000000	35%	105095
Taxes

 

[Peter_SSs]

By adding another helper cell, the Tax Rate and Mandatory Tax formulas could be even simpler.

Excel Workbook
	H	I	J	K	L	M	N	O
1	FilingStatus	Taxable Income	Tax Rate	Mandatory Tax		Start Row	End Row	My Row
2	3	300000	33%	23416.75		19	26	25
Taxes (2)

 

[AlphaFrog]

Peter_SS is correct. There are only 9 rows between each Status range. So if the Volatile functionality of Offset is not an issue...

=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],))

 

[dafan]

Thanks Peter you are correct.
And thanks Alfa I didnt get your formula at first too.