Formula to bring up data from one of three worksheets.

I have 4 columns of data in three worksheets D1, D2 and D3. In column E on each sheet is a helper column which refers to the data Input in a dropdown list C2 on a Summary sheet.
The present formula in columns B.C,D on the summary sheet can only draw data from sheet D1. I need help to find a formula that says if the information in C2 is not in sheet D1 then it looks in worksheets D2 or D3 for the information. The data in C2 will only appear on one of the sheets.
Currently C2 refers to KA1 3RG on worksheet D1 and if I change the data in C2 to KA1 4LY the sheet is blank because that information comes from sheet D3 which, in its present state, the formula does not cover.
A sample is attached, note that the full data is contained in 3 sheets with 900,000 rows in each sheet.
Any assistance would be gratefully appreciated
Thanks
Mikey

Hi, many thanks for your response but it seems the formulae only picks up data from D1 and not from D2 and D3. Not sure how I can reattach the file with the updated formulae in but would appreciate if you could try and resolve. If you put eg KA1 3UJ into C2 the info being extracted is from D1 and not D2 where the helper data is shown, also eg KA1 4LY shows no data when input into C2.
Would appreciate if you could have another look.
Rgds

Awesome, dont really understand it but it works each time.
Thank you ever so much
Best regards
Mikey