Hi

How about building your own function.

Using your example file, open a general module and enter the code
Function myfunc(id, dept, ee)
  holder = 0
  Set findit = ee.Find(what:=id)
  If Not findit Is Nothing Then
    firstadd = findit.Address
    Do
      If findit.Offset(0, 2).Value = dept Then
        holder = findit.Offset(0, 3).Value
      End If
      Set findit = ee.Find(what:=id, LookIn:=xlValues, after:=findit)
    Loop Until findit.Address = firstadd
  End If
  
  myfunc = holder
End Function
Then in sheet1!E2 enter: 
=myfunc(A2,D2,Sheet2!$A$2:$A$46)
Copy down as required.

HTH

rylo