Vlookup

For the problem presented below I am thinking of using multiple formulas from the lookup "family" but I am not sure if this can actually be done. If someone could give me an idea of how to do this it would be great.
It might be that this cannot be done without VBA, but before abandoning I thought I should ask more knowledgeable users of Excel formulas.

The problem:
In sheet1 I have the quantities sold from different products, the client buying them, and the date of the sale.

In sheet2 I have the prices of said products, each different according to the client that buys them. To add another level of complexity, the prices change in time, according to agreements that cover certain periods.

I need to calculate the value of each sale (that most often includes a number of different products) and for that I need the correct sale price for each of those products. Therefore I need to look in sheet1 at the date of the sale, and then look in sheet2 to see, for each product, where does that particular date fit between "start date" and "end date" (ie, what negotiated prices apply to that particular sale).

I don't know if I have managed to present this in a way that makes enough sense, so I have attached a sample file.

For example, in sheet1!R3 there should be a value of 800, because in "October 6" we sold to client "E"

"1" item of "prod1" at the price of "200" (because, given the sale date, the correct price is the one from the agreement starting in October 5 and ending in October 20)
+
"2" items of "prod3" sold to client "E" at the price of "300" each.

Another example:

In sheet1!R10 the value should be 2100 because in "October 24" we sold to the same client E

"1" item of "prod3" at the price of "900" (as seen in sheet2!F9 - because the agreement that applies for that sale date is the one starting October 21 and ending October 24)
+
"3" items of "prod7" at the price of "400" each.

For easy following of the examples, I highlighted the involved numbers in the 2 sheets.

I have constructed sheet2 in a way it makes sense to me. It may be that it can be structured in a better way. Sheet1 however cannot change structure.

Thanks a lot for any help you are able to give!

Based on your first post and expected outcomes perhaps:

Please Login or Register  to view this content.

Thanx DonkeyOte. It does seem to handle ok everything related to client E.
However, as you can see from some examples highlighted in red in this new attachment, the sale value is not calculated correctly for other clients.

I will try to modify the formula myself, but sincerely I don't understand yet how it works. Thanx again for pushing me to the right direction though.

It is my error.

If you look at the formula in R3 ... the very last reference to B3 should read as $B3 and not $B$3 as it did in my example.

(essentially using $B$3 means it is always looking for client as specified in B3 in each and every other row whereas the client should "adapt")