Excel 2007 : How to apply Multiple Calculations depending on Text from Drop Down List

**paradise710** · 02-24-2012, 01:25 PM

I have created a business expense report. What I am trying to do is calculate the state and federal tax depending on what state the expense was purchased in. I created a drop-down list of states in one cell. At the end of the table, gives you the break down of state (one cell) and federal tax (another cell). The problem is, each state tax is different. So I need the cell, both state and federal, to calculate based on what state is selected from the drop-down list.

-> Total fake tax for ND is 12%, 5% State, 7% Federal.
-> Total fake tax for SD is 15%, 6% State, 9% Federal.
-> Total fake tax for WY is 10%, 3% State, 7% Federal.

I attached an example.

Please help!

**NBVC** · 02-24-2012, 01:32 PM

You need to create a list of the states somewhere and in adjacent cells enter their corresponding taxes...

Then use VLOOKUP to get the associated tax for the drop down selection.

e.g.

If you table is in X1:Z10

then G8 would be:

=VLOOKUP(C8,$X$1:$Z$10,2,FALSE)

and in H8

=VLOOKUP(C8,$X$1:$Z$10,3,FALSE)

**DGagnon** · 02-24-2012, 01:34 PM

you will need to create a table of states and tax rates, for example in sheet2, column A and B. then in your formula use a vlookup to determin what tax rate to use.

your formula for cell G8 would be:

=SUM(Expense[[#This Row],[Meals]:[Hotel]])/1.12*VLOOKUP(Expense[[#This Row],[State]],Sheet2!A:B,2,0)