Using a formula to calculate a sumproduct between multiple dates, depending on the date

Good afternoon!

We have a spreadsheet that is causing some headaches, because the current method of the formulas as more or less done manually just referencing cells, and needing to be adjusted if there is a different date. What I would like to try and do (and I tried with an IF(OR) statement) is have the formula go off of the date of our product (Table A, ex row 31, 2/13/2017) and have the total price calculate correctly with the corresponding prices seen in table B, these tables are on different sheets (I do know the formatting needed)

The Formula that i would like to calculate is in Table A Column B, to calculate that if the date in Table A Column C is >= 11/30/16 and <1/7/17 then I want the Total price to calculate out =TableA!F8*TableB!B4+ TableA!G8*TableB!C4 +TableA!H8*TableB!D4. If it did not fall within that date range, then I would want it to push to the next date range >=TableB!A5 < TableB!A6 and so forth until all of them have been used to reference to get usable values. Would a nested IF statement work? Would I have to use a Vlookup? I am at a loss on how this could work to my favor and hoping that there might be a solution to assist with this, esp as this database grows a lot. Let me know if anything does not make sense or if I need to clarify, Thanks!


Table A

table a.PNG


Table B

table b.PNG