Combine Tables

[Heather560]

I need to combine the data in columns A and B with the data in columns D and E based on the matching data in columns B and D. I need the data in columns A and E to be repeated for each match. Example of output needed is in columns G-I. Any help is greatly appreciated.

[#2 · Accepted solution]

@Heather560 

Easiest way is with Power Query - query separately both range, merge them and return result back. Please see in Sheet2 attached.  

[#3]

@Sergei Baklan Thank you so much! Care to share how you did it? I am not familiar with power query.

[#4]

@Heather560 

On which Excel version you are? Power Query is built-in into Excel 2016 and later, for 2010 and 2013 you shall download and install Power Query add-in.

Next step it's better to transform your data into Excel tables. Stay within range, Ctrl+T, Okay. You will have Table1 and Table2 by default, but could rename if you wish.

Next query your data - stay on table, on ribbon Data->From Table. Power Query editor will be opened. Do nothing but File->Close and load to->As Connection.

Repeat with second table.

In right pane Queries and Connections double click on first query, it will be opened. Find in menu Merge->Merge as New query. Select second query to merge with, and for each column on which you'd like to join. Select type of connection (inner join). Okay.

Now Close and Load to->Table. Select location for the table. Okay.

In your case that's quite simple and doesn't require deep knowledge of Power Query. But it worth to learn more, the tool is quite powerful.