PLEASE HELP! How to combine two tables using multiple common values. Not opposed to VBA or formulas.

%3CLINGO-SUB%20id%3D%22lingo-sub-2276866%22%20slang%3D%22en-US%22%3EPLEASE%20HELP!%20How%20to%20combine%20two%20tables%20using%20multiple%20common%20values.%20Not%20opposed%20to%20VBA%20or%20formulas.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276866%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20tables.%20One%20table%20has%20a%20value%20in%20column%20A%20that%20is%20duplicated%2C%20and%20in%20column%20B%20there%20is%20a%20somewhat%20unique%20identifier.%20In%20the%20second%20table%20there%20is%20one%20value%20for%20column%20A%20that%20matches%20the%20first%20table%2C%20along%20with%20the%20unique%20identifier%20in%20column%20B%2C%20and%20finally%20a%20number%20in%20Column%20C.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20for%20example%3A%3C%2FP%3E%3CP%3E1st%20Table%3C%2FP%3E%3CP%3ETest1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BTry1%3C%2FP%3E%3CP%3ETest1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BTry2%3C%2FP%3E%3CP%3ETest2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BTry1%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2nd%20Table%3C%2FP%3E%3CP%3ETest1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BTry2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2.1443556%3C%2FP%3E%3CP%3ETest2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BTry1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2.9487392%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20I%20want%20to%20take%20the%20value%20in%20the%20second%20table's%20Column%203%20and%20add%20it%20to%20the%20corresponding%20rows%20of%20the%20first%20table%2C%20so%20that%20it%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinal%20Table%3A%3C%2FP%3E%3CP%3ETest1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BTry1%3C%2FP%3E%3CP%3ETest1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BTry2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2.1443556%3C%2FP%3E%3CP%3ETest2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BTry1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2.9487392%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20do%20this%20for%20hundreds%20of%20rows%2C%20please%20advise!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2276866%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2276879%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20How%20to%20combine%20two%20tables%20using%20multiple%20common%20values.%20Not%20opposed%20to%20VBA%20or%20formu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029100%22%20target%3D%22_blank%22%3E%40oreoicing%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20since%20365%20is%20mentioned%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20414px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F273527iDB3A9D1DC32BA7E9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(%5B%40Test%5D%26amp%3B%5B%40Try%5D%2C%0A%20%20%20%20%20%20%20%20%20Table2nd%5BTest%5D%26amp%3BTable2nd%5BTry%5D%2C%0A%20%20%20%20%20%20%20%20%20Table2nd%5BValue%5D%2C%0A%20%20%20%20%20%20%20%20%20%22-%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2276874%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20combine%20two%20tables%20using%20multiple%20common%20values.%20Not%20opposed%20to%20VBA%20or%20formulas.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276874%22%20slang%3D%22en-US%22%3EYou%20are%20amazing%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E!%20Thank%20you%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2276867%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20combine%20two%20tables%20using%20multiple%20common%20values.%20Not%20opposed%20to%20VBA%20or%20formulas.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029100%22%20target%3D%22_blank%22%3E%40oreoicing%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20second%20table%20is%20on%20Sheet%202.%3C%2FP%3E%0A%3CP%3EIn%20C1%20on%20the%20first%20sheet%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX('Sheet%202'!%24C%241%3A%24C%242%2CMATCH(1%2C('Sheet%202'!%24A%241%3A%24A%242%3DA1)*('Sheet%202'!%24B%241%3A%24B%242%3DB1)%2C0))%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdjust%20the%20sheet%20name%20and%20the%20ranges%2C%20then%20confirm%20the%20formula%20with%20Ctrl%2BShift%2BEnter%20to%20make%20it%20an%20array%20formula.%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have two tables. One table has a value in column A that is duplicated, and in column B there is a somewhat unique identifier. In the second table there is one value for column A that matches the first table, along with the unique identifier in column B, and finally a number in Column C.

 

So, for example:

1st Table

Test1     Try1

Test1     Try2

Test2     Try1 

 

2nd Table

Test1     Try2     2.1443556

Test2     Try1     2.9487392

 

And I want to take the value in the second table's Column 3 and add it to the corresponding rows of the first table, so that it looks like this:

 

Final Table:

Test1     Try1

Test1     Try2     2.1443556

Test2     Try1     2.9487392

 

 

I need to do this for hundreds of rows, please advise!

 

 

3 Replies

@oreoicing 

Let's say the second table is on Sheet 2.

In C1 on the first sheet, enter the formula

 

=IFERROR(INDEX('Sheet 2'!$C$1:$C$2,MATCH(1,('Sheet 2'!$A$1:$A$2=A1)*('Sheet 2'!$B$1:$B$2=B1),0)),"")

 

Adjust the sheet name and the ranges, then confirm the formula with Ctrl+Shift+Enter to make it an array formula.

Fill down.

@oreoicing 

As variant since 365 is mentioned

image.png

with

=XLOOKUP([@Test]&[@Try],
         Table2nd[Test]&Table2nd[Try],
         Table2nd[Value],
         "-")