|Yes, it's possible to write VBA code to do what you want, but it's not needed. Using VBA code to do what a formula will do just adds more complexity to the workbook, including issues with sending the workbook to others, code maintenance, etc.|
To find the Employee Designation and Place of Posting, a standard VLOOKUP will work. To find the salary, the OFFSET/MATCH/VLOOKUP combination gets the job done.
If I "guessed" at your data layout correctly, and the formula works as written on the 2 machines I've tried it on, including using the exact cells you posted in your response, then the problem is on your end, not with the formula construction. Did you modify the formula so that it is looking at the table in Sheet2?
I suggest you use Tools...Formula Auditing...Evaluate Formula to find out why you are getting a #N/A error.
FYI...to adjust the formula to read data from the table in Sheet2, the formula would look like this, where Sheet2 is the name of the sheet where your table exists:
I did this and it works across the 2 sheets.
If you insist on using a macro then this seemed to work for me. Right click the sheet tab for Sheet1, choose view code and paste this into the window that opens.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Cells.Count = 1 Then
Application.EnableEvents = False
With Sheets(2).Range("B1:B" & Rows.Count)
Set c = .Find(Target, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Target.Offset(0, 1) = c.Offset(0, 1)
Target.Offset(0, 2) = c.Offset(0, 2)
Target.Offset(1, 1) = c.Offset(1, 1)
If c Is Nothing Then MsgBox Target & " Not Found"
Application.EnableEvents = True