help with consolidating multiple IF arguments with multiple criteria

***RESOLVED***
What's the old saying? Work smarter, not harder? Well, the obvious answer completely alluded me until just this morning. I just added a column for each state (that will be hidden) that calculates the taxes for each state then, I will have one "tax" column showing with a formula similar to this: =IF([@[FOB State:]]="AK",[@[AK Tax:]],IF([@[FOB State:]]="ID",[@[ID Tax:]],IF([@[FOB State:]]="OR",[@[OR Tax:]],IF([@[FOB State:]]="MT",[@[MT Tax:]],IF([@[FOB State:]]="WA",[@[WA Tax:]],"")))))

***
I maintain an Excel 2010 spreadsheet consisting of extensive product information and pricing for items carried in multiple states. In it’s previous incarnation there were blocks of columns solely dedicated to the pricing of each state (as an example columns AA:AK would contain pricing information for the state of Alaska. Columns AL:AV would contain pricing information for the state of Idaho, so on and so forth, for 3 more states. Each state has different tax rates & calculations based on different criteria.

In an effort to make my spreadsheet more “Pivot Table friendly” and not so stinking wide, I have all the pricing calculating in one block of columns. What I need to do now is consolidate the formulas for tax calculations and add an “argument” (I’m not actually sure if that’s what it would be called, I am 100% self-taught and still VERY new) that will look at which state the taxes should be calculated for (I’ve added a column with a data-validation box to choose one of 5 states).

I need the formula in the “Tax” column (J) to do this:

1. Check the “FOB State” (column A) to determine first which state taxes apply.

2. If it’s AK, then this is the previous formula that calculated taxes correctly:

a. IFERROR(ROUND(IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE[#All],2)="Cider",IF([@[ABV:]]<=7%,[@[Gallons:]]*'Tax Rates'!$E$5,[@[Gallons:]]*'Tax Rates'!$E$6),IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE[#All],2)="Beer",[@[Gallons:]]*'Tax Rates'!$E$4," ")),2),0)

3. If it’s ID, then this is the previous formula that calculated taxes correctly:

a. IFERROR(ROUND(IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE,2)="Beer",IF([@[ABV:]]<=4%,[@[Gallons:]]*'Tax Rates'!$J$4,[@[Gallons:]]*'Tax Rates'!$J$5),IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE,2)="Cider",[@[Gallons:]]*'Tax Rates'!$J$5," ")),2),0)

4. If it’s MT, then this is the previous formula that calculated taxes correctly:

a. IFERROR(ROUND(IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE,2)="Cider",([@[Gallons:]]*'Tax Rates'!$O$8),IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE,2)="Beer",[@[Gallons:]]*'Tax Rates'!$O$7," ")),2),0)

5. If it’s OR, then this is the previous formula that calculated taxes correctly:

a. IFERROR(ROUND(IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE,2)="Cider",IF([@[ABV:]]<=7%,[@[Gallons:]]*'Tax Rates'!$U$4,[@[Gallons:]]*'Tax Rates'!$U$5),IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE,2)="Beer",[@[Gallons:]]*'Tax Rates'!$U$4," ")),2),0)

6. If it’s WA, then this is the previous formula that calculated taxes correctly:

a. IFERROR(ROUNDUP(IF([@[Country of Origin:]]<>"United States",[@[Gallons:]]*'Tax Rates'!$Z$5,IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE,2)="Beer",IF([@[ABV:]]<=10%,[@[Gallons:]]*'Tax Rates'!$Z$4,[@[Gallons:]]*'Tax Rates'!$Z$5),IF(VLOOKUP([@[Beverage Style:]],BeverageStyleTABLE,2)="Cider",IF([@[ABV:]]<=8.5%,[@[Gallons:]]*'Tax Rates'!$Z$6,[@[Gallons:]]*'Tax Rates'!$Z$7)," "))),2),0)

I hope this makes sense and that I haven’t made it more difficult than it needs to be.

Thank you in advance for any help offered.

Cheers!
Lu

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Ali, thank you so much for your kind suggestions and detailed instructions - I thought I must've been doing something wrong (trying to attach the example file originally)!

So, I think I've done as you asked (in mocking up the example) and the tax calculations are working, but only as it applies to each individual state, as this is the bit where I am struggling to consolidate them into one all-inclusive formula... I've tried to include most of the conditions the calculation might encounter and color code the sections based on the state - please note I've included the general tax rate "rules" by state, off to the side, to provide as full a picture as possible.

If I have neglected to include any pertinent information please let me know.

(fingers x'd the attachment part works)

Again, many thanks!