Returning a value if a given value exists in a certain range

I want to do a lookup formula with different sheets, in the first lookup sheet i have names of employees and work order name, in the second sheet i have just a short name of employee and work order name, so in my result sheet, if a work order finds its employee name from the look up number 2 sheet, then it should go to look up 3 sheet where employee short name and full name are there and pick the full name from there.
Please see the expected result i have highlighted in the attachment
Requesting help

Anyone can help me please?

Hi HansDouwe, I want the result in Column C only , Column D I have just showed what I wanted to achieve , can you modify the formula in Column C

You can merge these two formulas.
C2

Please Login or Register  to view this content.

Thank you windknife, it works

You are welcome.

I think you should also thank @Hans for his first good formula.

--------------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.