Finding tax percentage based on annual income & State

[Aon]

I need for the user to be able to input there annual salary, then select there state (from a pull down, that is generated from a list) I need a formula too take that information go through and find the persons state. Then basically scan through that individual states tax brackets & return the tax rate based on the income. I have played with this for a couple of days, I cannot figure out how to structure the formula. I’m assuming I need to use a combination of the “IF” function and the VLOOKUP.

Here are the facts:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Sheet 1<o></o>
<o></o>
B6 - User inputs annual income.
<o></o>
F2 – User selects state from drop down.
<o></o>
G3 – Tax percentage is displayed. (Formula needs to go in this cell)
<o></o>
Sheet 2
<o></o>
I have all states and there corresponding Tax brackets listed in
Columns - A, B & C Rows – 1:202
<o></o>
I have a the states drop down being generated from a list named “states”
As you can see it is in column F1:F53. The reason I didn’t just use column “A” is because I couldn’t get the drop down to ignore the blank cells. If there is a different way to do this, I’m all ears.

 

[jbvinny]

You need to create and IF(AND()) Statement. Within the IF AND would need to be VLookups based on the annual income amount. The merging of cells in column A will probably be a problem though.

Just a note. I havent tried this but I am assuming it would work. Dont have time to actually write the statement but I figured I would point you in the right direction.

 

[Peter_SSs]

Aon

Welcome to the MrExcel board!

Firstly, a hint about getting help in this forum: You will get more help here if you can post sample data that people can copy directly from the board to their test sheets, rather than having to type it all manually. You can do that with Excel jeanie - see my screen shots below.

Given your layout, I would attack this by using a helper column in Sheet2 as shown below. I have used column D but you could use any column and, if you want, it could be hidden once the formulas are entered.

Formula in D1 copied down to the end of the data.

Excel Workbook
	A	B	C	D
1	Alabama	0	2.00%	FALSE
2		500	4.00%	FALSE
3		3,000	5.00%	FALSE
4	Alaska	0	0.00%	FALSE
5	Arizona	0	2.59%	FALSE
6		10,000	2.88%	FALSE
7		25,000	3.36%	TRUE
8		50,000	4.24%	FALSE
9		150,000	4.54%	FALSE
10	Arkansas	0	1.00%	FALSE
11		3,700	2.50%	FALSE
12		7,400	3.50%	FALSE
13		11,100	4.50%	FALSE
14		18,600	6.00%	FALSE
15		31,000	7.00%	FALSE
Sheet2

Excel Workbook
	B	C	D	E	F	G
1					State
2					Arizona	Percentage
3						3.36%
4
5	Income
6	27,500
Sheet1

#VALUE!

 

[Domenic]

Here's another way...

Helper Cells:

G3:

=MATCH(9.99999999999999E+307,Sheet2!C1:C15)

H3:

=MATCH(F3,Sheet2!A1:A15,0)

I3, confirmed with CONTROL+SHIFT+ENTER:

=MIN(IF(ROW(Sheet2!A1:A15)-ROW(Sheet2!A1)+1>MATCH(F3,Sheet2!A1:A15,0),IF(Sheet2!A1:A15<>"",ROW(Sheet2!A1:A15)-ROW(Sheet2!A1)+1)))

Result Cell:

J3:

=VLOOKUP(F1,INDEX(Sheet2!$B$1:$B$15,H1):INDEX(Sheet2!$C$1:$C$15,IF(N(I1),I1-1,G1)),2,1)

Hope this helps!

 

[Aon]

Aon

Welcome to the MrExcel board!

Firstly, a hint about getting help in this forum: You will get more help here if you can post sample data that people can copy directly from the board to their test sheets, rather than having to type it all manually. You can do that with Excel jeanie - see my screen shots below.

Given your layout, I would attack this by using a helper column in Sheet2 as shown below. I have used column D but you could use any column and, if you want, it could be hidden once the formulas are entered.

Formula in D1 copied down to the end of the data.

Thank you so much, this worked perfectly. Thank you for everyone's help, I never would of figured that out on my own. I also took your advice and downloaded "Excel Jeanie"