Transposition Function with Multiple Sheets; non VBA/Macro

Hello,

I have attached spreadsheet 'Example 14-04-16.xlsx' for reference.

Outline
Three input sheets: List1, List2 and Input.
One output sheet: Master
The three input sheets are to be summarised, and filtered alphanumerically (by Order Number) into the Master sheet.

I am limited to only using functions; no macro, no VBA, and no inbuilt filters/functions can be used.

Situation
List1 shows Order Number, Person, Colour and Fruit
List2 shows Order Number, Person, Furniture and Animal
Input shows all six categories
Master summarises all projects into the six categories (depending on various criteria)

Considerations and Constraints
Point #1
If this spreadsheet was only made up of List1, List2 and Master, then the information in Master would be the comined information of List1 and List2. List1 Order Number would be matched to List2 Order Number and then the columns of both List1 and List2 would be summarised in Master. Which concludes three possible scenarios:
- Order Number only in List1 (therefore List1 information shown in Master)
- Order Number only in List2 (therefore List2 information shown in Master)
- Order Number in both List1 and List2 (therefore List1 and List2 information would be combined in the Master, under the one Order Number)

Point #2
List1 Order Numbers can have a suffix of "-D1", "-D2", "-D3", etc. (always of the format 'hyphen', 'D', '#')
Any order number that has this suffix will have the exact same information in the other columns. That is to say, Order Number 1234-D1 and 1234-D2 will both have the same person, colour and fruit.

Point #3
Any information in the Input sheet has priority, except blanks. Therefore if there is a:
- New Order Number, summarise accordingly in Master;
- Old Order Number, any value in the other FIVE fields (person, colour, fruit, furniture, animal), except for blanks, shall replace any value from List1 and List2 in the Master sheet.

Point #4
List1 and List2 are taken from other spreadsheets. These sheets may not be manipulated. They will be sorted alphanumerically (by Order Number).

Summary
- Transpose/Combine information from List1, List2 and Input into Master sheet.
- List1 and List2 Order Numbers can be all numbers, and numbers preceded by letters, however only List1 Order Numbers will be appended with "-D1", "-D2", etc.
- Any Order Number and Person will be the same for List1 and List2
- All information in Input, except for blanks, are to supersede any information shown in List1 and List2, when summarised in the Master.

Flexibility
As long as List1 and List2 are not altered, the option of inserting more sheets, creating dynamic cell ranges, working columns, etc. is all acceptable; just no VBA and no macros.

Any help I may receive in this instant is most appreciated, with previous experience having been beyond any expectation.

Thanks again,

-Yaddles