VBA to pull Multiple Values

I am worst in VBA, at my job we get report in excel and we need to fill in the EXCEL form using report, I tried to automate using formula but it increases the size of Sheet and at a point calculation stops because of too many formulas , it doesn't provide me required value,
I am looking for VBA, I have googled and found most vba part script that I require, Last thing I am looking for is VBA script for vlookup returning multiple values.
we paste report in Sheet2 and when we click Command button, forms should be Filled.Attached is the sample sheet, We paste the Report in Sheet1 and on basis of Department, forms should be filled in sheet2 (employee, Employee ID, Designation). I want VBA script for to pull Multiple Values.

Duplicate post

http://www.excelforum.com/excel-prog...ml#post3601229

Thank you Venkat,

What If there are more Data, I tried your suggested code, but its only pulling data for 2 Department, even if there are Multiple department.

Any help??