In my source data sheet, starting in A1, I have the following information:

Property Dept Total Reported Wk 1 Wk 2 Wk 3 Wk4
ABC Finance 7 1 2 4 0
ABC Sales 2 1 1 0 0
XYX Finance 85 22 26 23 14

I then have one worksheet for each property (property name in A1), with the
departments vertically listed, starting in column C:

Department Total Reported Wk 1 Wk 2 Wk 3 Wk 4
HR
Finance
Sales

I need for cells D2:H2 to look up the property name from A1, the department
from C2 and then fill in the matching values for Total Reported and Wks 1-4.
I want it to be blank or display a 0 if there are no corresponding values
(i.e. HR has no reports).

Is this possible? I'd prefer to automate with a function rather than an
advanced filter paste that would require almost as much work as just
copying/pasting the data from the data source sheet to the property-specific
sheet.

Thanks in advance!