searching a range within a range

Hi. Me = macro neophite, mostly just record macros and tweak as need be. But need help with this one.

Trying to enter pay rates into a data sheet when an employee works in a specified department.

My logic may be off, but what I want to do is this:

for each cell in A:A
the cell = employee#
if (the cell in column D is not null AND the cell in column e is blank) then the cell in column D is the department

go to sheet to and for each cell in range A:A of sheet 2
if the active cell = employee and the corresponding cell in column c = dept, then copy the corresponding cell in column D of Sheet 2 and paste it into column E of the first sheet.

I attached a sample of the data. The actual sheets are hundreds of pages long and will take me a couple days to go through without a macro - so if anyone is in a generous mood, I'd appreciate the help.

Thanks.

I know it is really wrong and its embarraising for me to post it, but this where I got so far and decided that I needed help

Sub Macro2()
'
' Macro2 Macro
Dim ee
Dim dept
Dim x As Range
Dim y As Range

On Error Resume Next

For Each x In Range("A:A")

Set ee = ActiveCell.Value

If Selection.Offset(0, 3) Is Not Null And Selection.Offset(0, 4) = "" Then Set dept = Selection.Offset(0, 3).Value
    Sheets("Sheet2").Select
    For Each y In Range("A:A")
        If y = ee And Selection.Offset(0, 2) = dept Then Selection.Offset(0, 3).Copy
        Sheets("Sheet1").Select
        Selection.Offset(0, 3).PasteSpecial
    Next
Next
End Sub

I am not exactly sure what you are doing
This would work in E2 and dragged down
=SUMPRODUCT(--(Sheet2!$A$2:$A$46=A2),--(Sheet2!$C$2:$C$46=D2), --(Sheet2!$D$2:$D$46))
I do not see what determines true or false