HELP...... Please!

Data Set Example (Sheet2):
A B C D E
1 EmpID Dept Site Start End
2 10 A Yellow May'07
3 500 B Green May'07 Jun'07
4 26 C Brown May'07
5 500 A Pink Jun'07

Step 1:
I have another source (Sheet1) that provides the EmpID (A1 = 500) & the Dept
(B1 = A) and I need a formula that will give me the Site from the above in C1
on sheet1(Should = Pink).

Step 2:
However as you can see, the EmpID table above can have duplications and so I
need the formula to look at both columns (in the row) at the same time and
not match any entry in one column with any entry in the other column.

NB:
The date columns may need to be brought into play, but I would prefer the
formula not to need to use these.

Many thanks for any help possible.
Steve