Excel Join Tables On Column

Select cell F3 and click on it. Insert the formula: =INDEX ($H$3:$I$9, MATCH (C3, $H$3:$H$9, 0), 2) Press enter. Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell. Using the INDEX and MATCH functions to join two tables. Create the tables you want to join and then click the 'Join tables' button to get the following window opened. Left table: Select the left table you want to join with. Left table output columns: Select the output columns from the left table that will show in the result data. Note that the key column can not be deselected.

DigDBAdd-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?(more ...)
Features Testimonial Downloadfree 15-day trial Purchase FAQ Contact

Join (merge) Tables (lists) - by columns match in Excel

JoinJoin is to combine two tables by matching the values in corresponding columns. In result, you will get a merged table which consists of the first table, plus the matched rows copied from the second table.

For example, suppose you have 2 tables (lists) - 'Race' and 'Horses' - they both have a column 'horse_name'. You want to join (merge) the 2 tables into 1 combined table, so that in the 'Race' table, you can also see each horse's age, color, body mark (i.e., the info located in 'Horses' table).

Join is a tough database operation which Excel does NOT support. Often, we hear users ask us, for example, '...I have data on 2 worksheets. I need to copy data in 1 column from one worksheet to the other if data in 2 other columns match. Does your program do this? I hope this makes sense...'. Well, what they really need is a join. Do not waste time trying to get Excel's Match, Lookup, or VLookup to work. DigDB's join is what you really need.

Join is crucial for cross-table roll-up summary. For example, if you only have the above 'Race' table, you can do a roll-up summary of Average Finish of each Rider; But in the joined 'Race' and 'Horse' table, you can do a summary of Average Finish of each Horse Age group. This summary is only possible after you join the two lists into one list to have all the needed columns.

Excel join tables based on column values

Join ( merge ) Tables Demo

  1. Go to List1 - the Race table. Click only 1 cell, then invoke 'DigDB->Table->Join (Merge)...', a wizard will start.


    Practice file - join-merge-table-demo.xls (16k)

  2. Wizard Step 1 - List1 is automatically selected.
  3. Wizard Step 2 - Select List2.
    • Click into the range selector
    • Then you can go to the List2 sheet or area, click only 1 cell in List2
    • Click 'Select Table(List)' button, to auto-select List2
  4. Wizard Step 3 - Use the dropdown to select the pair of columns to match. Our case only needs one pair. To add more pairs, click 'Add Criteria'. This allows you to select joining by multiple column matching.

    Note that the column names do NOT have to be the same.

  5. Wizard Step 4 - Select what to be included in merged table

    The default is to include all rows from List1, and include only the matched rows from List2. And all columns from two tables are included. But you can select different options.

  6. Joined (merged) table will be put in a new sheet - by default, it includes all rows from List1, the matched rows from List2, all columns from both.

    Note that there is a new column Count of Matches. It is possible that when you join two lists, for a row in List1, there are 2 matched rows in List2. In this case, only the 1st matched row from List2 is included in the merged table, and the Count of Matches value will be 2.

    In our example, the Count of Matches is all 1. This means all the matches are one-to-one matches.

Join (merge) options for selecting rows

    Step4 of the Join wizard allows you to select how to merge the two tables. You can select:

    We will use the following two simple tables as an example to illustrate the effect:


    Practice file - join-merge-table-simple.xls (16k)

  1. Result for All Rows from List1, Matched Rows from List2 - this is the default, equivalent of a left-join
  2. Result for All Rows from List1, All Rows from List2
  3. Result for Matched Rows from List1, Matched Rows from List2
  4. Result for Matched Rows from List1, All Rows from List2

Join (merge) options for selecting columns

    By default, all the columns are included in merged table. But you can click the 'Select...' button to select the columns that you want to include in the result table.

Need more?

  • Browse the complete list of DigDB's powerful features listed on the front page.
  • If you see errors or things not working, check out our error reference page.

Excel Join Tables On Columns

Features Testimonial Download Purchase FAQ Contact

Excel Join Tables With Same Columns

Copyright © 1999-2007 All rights reserved
DigDBAdd-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user


Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.

June 14, 2018 - by Bill Jelen

David from Florida asks today's question:

I have two workbooks. Both have the same data in column A, but the remaining columns are different. How can I merge those two workbooks?

I asked David if it is possible that one workbook has more records than the other. And the answer is Yes. I asked David if the key field only appears once in each file. The answer is also yes. Today, I will solve this with Power Query. The Power Query tools are found in Windows versions of Excel 2016+ in the Get & Transform section of the Data tab. If you have Windows versions of Excel 2010 or Excel 2013, you can download the Power Query add-in for those versions.

Here is David's workbook 1. It has Product and then three columns of data.

Here is David's workbook 2. It has Product Code and then other columns. In this example, there are extra products in workbook2, but the solutions will work if either workbook has extra columns.

Here are the steps:

  1. Select Data, Get Data, From File, From Workbook:

  2. Browse to the first workbook and click OK
  3. In the Navigator dialog, choose the worksheet on the left. (Even if there is only one worksheet, you have to select it.) You will see the data on the right.
  4. In the Navigator dialog, open the Load dropdown and choose Load To...
  5. Choose Only Create a Connection and press OK.
  6. Repeat steps 1-5 for the second workbook.

    If you've done both workbooks, you should see two connections on the Queries & Connections Panel on the right of your Excel screen.

    Continue with the steps to merge the workbooks:

  7. Data, Get Data, Combine Queries, Merge.

  8. From the top drop down in the Merge dialog, choose the first query.
  9. From the second drop down in the Merge dialog, choose the second query.
  10. Click on the Product heading in the top preview (this is the key field. Note you can multi-select two or more key fields by Ctrl + Clicking)
  11. Click on the Product Code heading in the second preview.
  12. Open the Join Type and choose Full Outer (All Rows From Both)

  13. Click OK. The data preview does not show the extra rows and only shows 'Table' repeatedly in the last column.

  14. Notice there is an 'Expand' icon in the heading for DavidTwo. Click that icon.
  15. Optional, but I always unselect 'Use Original Column Name As Prefix'. Click OK.

    The results are shown in this preview:

  16. In Power Query, use Home, Close & Load.

Here is the beautiful feature: if the underlying data in either workbook changes, you can click the Refresh icon to pull new data in to the results workbook.

Note

The icon for Refresh is usually hidden. Drag the left edge of the Queries & Connections pane to the left to reveal the icon.

Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2216: Combine Two Workbooks Based on a Common Column.

Hey, welcome back to MrExcel netcast, I'm Bill Jelen. Today's question's from David, who was in my seminar in Melbourne, Florida, for the Space Coast Chapter of the IIA.

David has two different workbooks where Column A is in common between both of them. So, here's Workbook 1, here's Workbook 2-- both have product code. This one has items that the first one doesn't have, or vice versa, and David wants to combine all the columns. So, we have three columns here and four columns here. I put both of these in the same workbook, in case you're downloading the workbook to work along. Take each one of these, move it out to its own workbook and save it.

Alright, to combine these files, we're going to use Power Query. Power Query's built into Excel 2016. If you're in the Windows version of 10 or 13, you can go out to Microsoft and download Power Query. You can start from a new blank workbook with a blank worksheet. You're going to save this file-- Save as, you know, maybe Workbook, to show the results of combined files .xlsx. Alright? And what we're going to do is, we're going to do two queries. We're going to go to Data, Get Data, From File, From Workbook, and then we'll choose the first file. In a preview, select the sheet that has your data, and we don't have to do anything to this data. So just open the load box and choose Load To, Only Create Connection, click OK. Perfect. Now, we're going to repeat that for the second item-- Data, From File, From a Workbook, choose DavidTwo, choose the sheet name, and then open the load, Load To, Only Create a Connection. You'll see over here in this panel, we have both connections present. Alright.

Now the actual work-- Data, Get Data, Combine Queries, Merge, and then in the Merge dialog, choose DavidOne, DavidTwo, and this next step is completely unintuitive. You have to do this. Choose the column or columns in common-- so Product and Product. Alright. And then, be very careful here with the join type. I want all rows from both because one might have an extra row and I need to see that, and then we click OK. Alright. And here's the initial result. It doesn't look like it worked; it doesn't look like it added the extra items that were in file 2. And we have this column 5-- it's null now. I'm going to right click column 5 and say, Remove that column. So open this expand icon and uncheck this box for Use original column name as prefix, and BAM! it works. So the extra items that were in File 2, that aren't in File 1, do appear.

Alright. Now in today's file, it looks like this Product Code column is better than this Product column, because it has extra rows. But there might be a day in the future where Workbook 1 has things that Workbook 2 doesn't have. So I'm going to leave both of them there, and I'm not going to get rid of any nulls because, like, even though this row at the bottom appears to be completely null, there might be in the future a situation where we have a few nulls in here because something's missing. Alright? So, finally, Close & Load, and we have our sixteen rows.

Now, in the future, let's say that something changes. Alright, so we'll go back to one of those two files and I'll change the class for Apple to 99, and let's even insert something new and save this workbook. Alright. And then, if we want our merge file to update, come over here-- now, watch out, when you do this the first time, you can't see the Refresh icon-- you have to grab this bar and drag it over. And we will do Refresh, and 17 rows loaded, the watermelon appears, the Apple changes to 99-- it's a beautiful thing. Now, hey, do you wanna learn about Power Query? Buy this book by Ken Puls and Miguel Escobar, M is for (DATA) MONKEY. I'll get you up to speed.

Wrap-up today: David from Florida has two workbooks that he wants to combine; they both have the same fields in Column A, but the other columns are all different; one workbook might have extra items that are not in the other and David wants those; there's no duplicates in either file; we're going to use power query to solve this, so start in a new blank workbook on a blank worksheet; you're going to do three queries, first one-- Data, From File, Workbook, and then Load to only Created Connection; the same thing for the second workbook, and then Data, Get Data, Merge, select the two connections, select the column that's common in both--in my case, Product-- and then from the Join Type, you want to full join all from the File 1, all from File 2. And then the beautiful thing is if the underlying data changes, you can just refresh the query.

To download the workbook from today's video, visit the URL in the YouTube description.

Column

Excel Join Two Tables Based On Column

Well, hey, I want like David for showing up for my seminar, I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.

Download Excel File

Excel Insert Column In Table

To download the excel file: combine-based-on-common-column.xlsx

Power Query is an amazing tool in Excel.

How To Add Column In Excel Table

Excel Thought Of the Day

How To Combine Columns In Excel Table

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

Add Table Column Excel

'Always press F4 when you read range or matrix in a function'