Perform Lookup across the tabs using VBA

[chauhananirudh2012]

I am facing an issue and don't know where to start from what i am going talk about consist of 4 worksheets.
Sheet1 consist of following data



Sheet2 consist of following data


Sheet3 consist of following data


And finally the Output sheet should look like


I want to achieve this output through VBA or using any shortcut function like index-match. Please assist how can I start or achieve this to avoid using multiple vlookups