 New Contributor

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

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

3 Replies

Re: Need to combine two tables using multiple common values. Not opposed to VBA or formulas.

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.

Re: Need to combine two tables using multiple common values. Not opposed to VBA or formulas.

You are amazing @Hans Vogelaar! Thank you so much!

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

As variant since 365 is mentioned with

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