[SOLVED] Lookup Specific Date from a List of Date Ranges & Return Related Data

Hi all,

I need some help with the problem below. I am using Excel 2007. I have tried searching for a similar problem that someone elase may have already asked, but none seem to help with my particular problem, so here goes:

Please see the arrached spreadsheet file.


I need some sort of lookup formula to go in column B of the "Weeks" sheet that will:
- look at the date in column A of the "Weeks" sheet.
- find that date in the appropriate range (column E - F) on the "Clinics" sheet.
- return a value of (Col A x Col B) from the row in question.

For example:
In row 2 of the "Weeks" sheet we are looking for the date 28/5/12. Looking on the "Clinics" sheet, that date falls in the date range on row 2. So A2xB2 = 165 and this is the figure I need to be displayed in cell B2 on the "Weeks" sheet.


I hope I have made myself clear and that I have posted this query in the correct place. As you will see, I am new to the forum. Thanks in advance for any help you could offer,
mrmessy.