Indexing Multiple Tables

I've been doing quite a bit more in Excel lately and have even taken a class or two that was supposed to be "Advanced". Most everything I've learned in these classes are things I've already self taught, and for my latest endeavor, very little help.

I've simplified my example for security and brevity. Actual worksheet is 38 columns by 7000+ rows.

Here's the situation:

Current customer product inventories for monthly recurring services are listed in the A4:C58 table. What I am trying to accomplish is to write a formula with parameters of Product, State, and Term/Qty based on the product pricing tables to automate the process of pricing out the new promo price for column D. Also, since there are products that are not eligible for promotion, have those return the current price listed.

I've been trying to use INDEX in an IF or VLOOKUP but can't quite map it out to work.

Any help would be appreciated.