Excel 2007 : Index with Duplicates

Have a look... a change on the "master" sheet so you can just enter any department on any and flag it as a "manager" message.

Each department sheet is now identical. You can create a new department sheet by simply copying one of the existing sheets and renaming the sheet. It's the name of the sheet that triggers the value in A1 causing those items to be collected.

The array formulas on the sheets do the heavy lifting.