Index and Vlookup question (Multiple Condition)

[jp16]

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?

[jp16]

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.

[vlady]

can you try this one
=index(D:D,match(A1&B1&C1,Sheet1!A:A&Sheet1!B:B&Sheet1!C:C,0)) -ctrl+shift+enter

[jp16]

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?

[Richard Buttrey]

Hi,

When you say you have another 'spreadsheet' do you really mean that you have another Excel workbook, or is there in fact only one workbook with the data you show in one sheet and the A, B & C columns without column D in a second worksheet?

Assuming the former and the key field is the contract number, then in column D of the second workbook enter the following formula in D1 and copy down

=VLOOKUP(C1,'C:\[Book1.xls]Sheet1'!C:D,2,False)

Change the Book1 and Sheet1 references as appropriate.

[jp16]

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.........

[jp16]

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

[JBeaucaire]

Select Thread Tools from the menu above to mark the thread as solved. Thanks.