VLOOK UP and = to help

Mmmm. There are lots of pitfalls here. Firstly, you have a lot of merged cells (yellow shading on the two weekly sheets). Can you get rid of them? They will make maintenance of any formulae a TOTAL nightmare. They are best avoided entirely.

Secondly, do I understand you correctly that you want to choose the entries in the 3 tan coloured cells in sheet 1 from dropdowns and then get Excel to do its stuff? If so - what is the point of the DD in D2. Is the same information not being displayed in D5? If so - why have it displayed twice?