Multiple Entries Under One Reference on a table - Lookup Values?

Hi everyone,

Thanks in advance anybody's help on this, i'm self taught with excel so am not advanced and this has been a bit of a head scratcher for a while and i have given in and come to the experts!

I have a table which is full of timesheet data. Each timesheet refers to a particular work placement. I therefore want to map this data onto a calendar.

On the attachment you will see a small extract from my table and below it a mock-up of where i want to get to.

Example.xlsx

So i want to return the Timesheet value for the Calendar week. So if the entry on the table matches the entry in the Calendar AND the End Date of the timesheet falls inbetween the dates on the Calendar then return that Timsheets Value. I have tried using VLookup but because they share the Assignment ref it will only pick up the first entry.

Any help would be greatly appreciated!

Jamie