multiple lookup including date range

I have a need to be able to lookup on three different criteria one of which is a date which would fall between a start and end date. Example below.

My main data is product price by customer and date range.

Customer Material Price Start Date End Date
100008 10220 7.2 21/05/2006 08/07/2006

What I'm trying to do is get the price out based upon the date of an invoice. So I have a second workbook that shows the following:

Customer Material Date of Invoice Price
100008 10220 06/06/2006 Should read 7.2
100008 10220 10/07/2006 Should fail.

providing the customer and materail match and the Invoice date is within the range of start and end dates I need to be able to pull the price in to the price field on the relevant workbook.

TIA

Jon

Originally Posted by Carim

Hi Jon,

You do need a sumproduct() formula ...
Feel free to upload a zipped sample of your worksheet for a precise answer ...

Hi Carim,

I've attached example of the spreadsheet.