Excel Project Planner - Help with Lookups and References

I am trying to put together a basic project planner to organise the planning and analysis of various retail campaigns over the period of two years across three departments within the business.
I have a tab for each campaign, which includes a column for year, week number (calendar week number, used in our business for timings), task, and department responsible.
Further to the campaign tabs, each of the three departments will have their own tab, to show their own tasks only and the campaign it is linked for. Obviously different tasks for different campaigns will take place in the same week, and it is therefore easier for each department to have their own tab, instead of flicking through each campaign’s tab to find out what’s on for this week.
I am very familiar with V and HLOOKUP functions, but I cannot see it being applied here. I would much prefer formulas to macros. Basically, the “common” data to all tabs which could potentially be used for a VLOOKUP would be the department reference sign. (for business privacy reasons I have labelled the departments in the attached example as “Dept1” “Dept2” and “Dept3”).
Perhaps I could get the Departmental tabs to scan all campaign tabs for their dept reference sign and copy the task across? However, I need the week number and year to follow over too.
Further to this, there may be difficulties where two departments need to collaborate on a task, which I have indicated as: Dept1 & Dept2 in the “responsible” column.
Also, in the example “Campaign1” you will see week 48 appears twice, as two important tasks are relevant in that week, sometimes for the same dept.
I think already that the format of the departmental tabs may need to be changed, (e.g. perhaps week numbers should go across in rows) I am happy for this to happen.
Can anyone advise how best to go about this?