Index and Vlookup question (Multiple Condition)

Hi, my excel is as below. I have excel sheet. Column A, B, C, D stand for Room No, Company, Contract No and Rental.
Spreadsheet 1 look like this...

A2-01 ABC C201201002 $500
A2-03 ABC C201201003 $450
A2-01 GGG C201201001 $350
B2-03 GGG C201201001 $260
B2-03 XXX C201201005 $250


The difficult thing is each room might have 1 or more contract number and maybe more than 1 company staying. 1 company might have multiple contract. However, each contract is only attached to 1 company. For example, ABC might have Contract number C201201002 and C201201003, but these 2 contract number wont be re-used again for another company.

NOw the problem comes, I have another spreadsheet, it have the same Column A, B and C. HOwever, without Column D. How do I use "Index" or "Vlookup" to solve my problem?

Sorry, I mean i need to input the value at Column D to the other spreadsheet. I cannot just copy n paste the whole thing.

Hi, i tried, but i get error. I don't know why.
Does your formula =index(D:D,match(A1&B1&C1,Sheet1!A:A&Sheet1!B:B&Sheet1!C:C,0)) mean, "Go Down Column D, and Stop at where A1 is found in Column A, B1 Found in Column B, C1 found in Column C"?

Also, some Contract in Spreadsheet 2 (Without Column D) didnt not appear in Spreadsheet 1 (With Column D). Can i add a "if(iserror(match......)" to your formula? And Shift+ctrl + enter? Would it be ok?

What vlady post has given me some hint. But i don't know how to input the formula. I use index. But the problem is that all the value might appear more than once. And excel automatically give me the most upper search result when i use Match. I need a Double Match. match the Contract then match the Room.........

Hey, anyone can tell me where is the "solve" button? My problem is solved. Thanks vlady!!!!