How to use Merge Two Tables for Excel

The Merge Tables Wizard add-in can match and merge data from two Excel worksheets in seconds. This smart tool is an easy-to-understand and convenient-to-use alternative to Excel Vlookup/Index+Match functions.

Video: How to merge two tables in Excel

Before you start

Excel instances

Open the Excel workbooks that contain the tables you are going to compare. Both tables should be opened in the same instance of Excel.

Hidden rows

If you have hidden rows in your main and lookup tables, they won't be processed.

Backup copies

Pay attention to the Create a backup copy of the worksheet checkbox. We recommend keeping this option selected as Excel doesn't let you cancel changes made by add-ins.

How to use Merge Two Tables Wizard

Start Merge Two Tables

On the Ablebits Data tab, in the Merge group, click Merge Tables > Merge Two Tables:
Merge Two Tables in Excel.

Step 1: Select your main table

The main table is a table you merge with a lookup table. Your data will be updated only in the main table.
Select the main table.
There are 5 ways to select the main table:

Before running the add-in:

  • Select any cell in your dataset before running the add-on, and the whole table will be highlighted automatically.
  • To limit the range to certain data, select your records, and the add-in will pick up your selection.
    • Note. If your records are formatted as a table, the add-in will always get the entire table.

After running the add-in:

  • Click the Select range icon in the Merge Tables Wizard window and select your data.
    Select the needed range.
  • Select any cell in your dataset, and then click the Expand selection icon in the add-in window to expand the selection to the entire table.
  • Type the range address in the Select your main table field manually.
Note. If you have the standard Excel auto filter option switched on, the add-in will match only the visible filtered rows. If you need to update the entire table, turn the filter off before starting the add-in.

Click the Next to continue.

Step 2: Pick your lookup table

The lookup table is a worksheet or range where you search for (look up) matching data. The add-in will pull information from this table. The lookup table remains intact after the add-in merges two tables.
Choose the lookup table.
On this step, you see all the open workbooks and worksheets in the Select your lookup table area. Choose the Excel worksheet with your lookup table and the add-in will highlight the used range.

Tip. You can edit the range by clicking the Select range icon or simply using your mouse cursor to select it in your table.

Click Next to continue.

Step 3: Select matching columns

Your tables may have several columns in common. Key columns are the important ones that let you identify the same records in your sheets, for example, IDs or the combination of First and Last names. Please note that the values in these columns are only compared; you will be able to select the columns to update on the next step.

Here you can see a table with a list of all the columns you have in your main sheet. Tick the checkboxes next to the columns you need to compare. Once chosen, the add-in will automatically pick a column with a matching header in a lookup table if there is one. If there is no match, please select one in the drop-down list of Lookup table columns.
Pick matching columns.

  1. If your tables have header rows, select the Main table has headers and/or Lookup table has headers checkboxes. If your tables do not have headers, clear these boxes. In this case, the contents of the 1st row will be displayed to help you match the right records.
  2. If text case in the key columns is important to you, tick off the Case-sensitive matching checkbox at the top. This will instruct the Merge Tables Wizard to distinguish between uppercase and lowercase letters in the values it compares.
  3. Click Auto Select to get all the columns selected as matching. If you ticked the Main table has headers and/or Lookup table has headers checkboxes, only columns with corresponding headers will be selected.
  4. Pressing Unselect All will remove selection from all columns.
Tip. If you have a lot of columns in your tables, you can expand the wizard window by dragging the bottom-right corner down and to the right until you get a suitable size.

Click Next to continue.

Step 4: Choose the columns to update in your main table

On this step, select the columns you want to update in the main table and pick the corresponding columns from the lookup table with the new values:
Select the columns to update.
You can also click the Auto Select button to select all matching columns at once. Press Unselect All to remove selection from all columns.

Tip. If you have a lot of columns in your table, the counter at the bottom of the add-in window will help you keep track of how many you select.

Click Next to proceed.

Step 5: Pick the columns to add to your main table

On this step, you can add other columns from the lookup table to the main table. You will see a list of Lookup table columns that were not selected as a source for comparison or update on the previous steps.

Tick off the checkboxes next to the columns you want to insert into your main worksheet:
Choose the columns to add.

Tip. Select or deselect all columns at once by using the buttons Select All and Unselect All respectively.

Click Next to proceed.

Step 6: Choose additional merging options

The last step offers advanced options that let you fine-tune the merge. All these options are applied to the main table:
Adjust the merging options.

Add rows and columns

  • Add non-matching rows to the end of the main table
    Non-matching are the rows with the key values that are not present in your main worksheet.

    For example, you match tables by the column with IDs. The main table has IDs from 1 to 15. The lookup table contains IDs from 1 to 20. So, the IDs from 16 to 20 in your lookup table are non-matching. When you choose Add non-matching rows to the end of the main table, the rows with such values will be inserted after all rows in the appropriate columns of the main table:
    How to add non-matching rows.

  • Insert additional matching rows
    Select this option to add rows with duplicate key values that may contain unique information in other columns. You can paste additional matching rows at the end of the table or after the row with the same value in the key column.

    • Attach repeated rows after all data in the main table by choosing the At the end of the main table option:
      Additional matching rows at the end of your table.
    • Or insert additional rows from the lookup table right after the same key value in your main table and get all the duplicates grouped together. For this, select After the row with the same key value:
      Collect all dupes together.

      Tip. You can run Merge Duplicates to combine these rows and keep all unique information in place.
  • Set background color for all added rows
    Choose this option to mark the added rows with a background color.
  • Add a status column
    If you tick this option, the add-in will add a new column to your main table and mark rows as Matching, Matching and updated, or New row.

Update cells in the main table

This group lets you specify how to deal with empty cells in the main table.

  • Empty cells only
    Select this option if you want to update only empty cells. The existing values in your main table will not be overwritten.
  • Only if cells in the lookup table contain data
    Choose this option if you may have empty cells in your lookup table, and you don't want them to overwrite the existing values in your main table.

Highlight cells

You can highlight all updated records in your main table with color by ticking off this checkbox and picking an appropriate hue from the drop-down list. This way you will easily see the changes after joining tables.

Once you select all the options you need, click the Finish button and enjoy the results.
Merge two lists in Excel.

Responses

Pasin Songphonnoppachon says:
December 30, 2021 at 7:20 am

I install a trial download but it wasn't work with my MS 365. I would like to buy ULTIMATE SUITE FOR EXCEL.
I need to ensure it could be work and I will purchase order. Can you feedback & what I should do.

PS

David Florey says:
October 26, 2021 at 4:11 am

No use, as soon as the add in was installed, my trial was over and none of the tools were usable.
Maybe include a free trial period before expecting users to pay...

Daria Markelova (Ablebits Team) says:
October 26, 2021 at 2:17 pm

Hello David,

I'm sorry that you've faced such a problem with our software.

Please note that the Merge Two Tables tool is available as part of our Ultimate Suite for Excel. There is a fully functional trial version of the add-in that you can install and test out for free. You can download it from this webpage.

In case you have installed one of the editions earlier and the trial period has expired, you can install another edition of Ultimate Suite (e.g., Business instead of Personal or vice versa) and test out the tools more.

If any further assistance is needed, feel free to email us at support@ablebits.com.

Nick Paul says:
April 29, 2021 at 2:46 pm

Hi,

i cant figure it out why this doesnt work.

I have a productfeed with 12.000 products, all have stock.
Also i have a stockfeed which contains 300.000 items.

All products have an unique SKU, the productfeed contains all unique SKU's.
I want it to update the stock. The stockfeed is updated every 1 hour, so i want to do the following:

1. CSV file with products is named productfeed.csv.
2. CSV file with stock is name stock.csv

I want to transfer the stock in stock.csv to productfeed.csv, but should match the stock with the SKU.
Do you understand what i mean? Can you please help?

Rob Jackson says:
July 15, 2020 at 6:19 pm

I use the merge table function to update data from one file to another. Why do I continue to get addition (identical rows) being added to the new spreadsheet? The files have exactly the same headers (with the exception of three additional columns. My data matches correctly with the headers, and I unselect the one row that I want to be updated.

NASSER JARRODI says:
May 6, 2020 at 1:43 pm

Hi, I use the free trial and when i want to merge 2 tables, it skip step 4 & 5, it doesn't let me to add column to main table? please advice.

Max says:
February 20, 2020 at 9:11 am

Hi
Quick Question
Is there any way to save a previously used "Merge Two Tables" process so the next time it is needed I do not need to set it up again?
Thanks
Max

Katerina Bespalaya (Ablebits Team) says:
February 20, 2020 at 12:24 pm

Hi Max,

Thank you for your question. I am sorry, but there is no way to save the merging process in the current version of the add-in. You should run the add-in each time you need to merge your tables, but the options you have chosen during your last merge should be retained. Please let us know if you have any other questions.

Kay says:
July 24, 2019 at 9:15 am

Hello,

I hope that you could be able to answer my question. Well I have this 2 different sheets and I wanted to merge them but the biggest puzzle are:
1. both Sheets have product IDs
2. The first sheet contains complete details like prices, roi etc. but does not have SKU
3. The 2nd sheet have only ID and SKU
4. Now my client wants to look up price values from the 1st file using IDs and put it under the second file, my problem is I am using a Vlookup and already searched for possible ways to do this, I cannot find a unique Identifier that could pull these records accurately since both of these files contains duplicate values of IDs and does contains different prices
5. It is working on my Vlookup query however on the duplicate records, it only returns the first value results.
6. What I want is to return unique price value of each Duplicate ID inline with the id row while retaining the order of the 2nd file. Because some of the solutions I found is to lookup the result chronologically.

I hope that you could help me. Thanks in advance.

Katerina Bespalaya (Ablebits Team) says:
July 24, 2019 at 12:00 pm

Hello Kay,

For us to be able to help you better, please send us a small sample workbook with your source data and the result you want to get to support@ablebits.com. I kindly ask you to shorten each table to 10-20 rows / columns. We'll look into your task and try to help. Thank you.

Urban says:
July 11, 2019 at 11:57 am

Hi!
What if I want to use the "merge two table" feature on a daily basis, is there a way to simplify the process, e.g. via a macro or such?

Katerina Bespalaya (Ablebits Team) says:
July 11, 2019 at 1:57 pm

Hi,

I regret to tell you that the add-in can't be called via a macro. You need to run the Wizard each time you want to process the data.

Please let me know if you have any other questions.

Julia says:
January 9, 2019 at 4:05 pm

I am using the Merge Two Tables wizard and recently, I've had issues on step 3. When selecting my main column and trying to select the column from the Lookup table columns, they are all blacked out. I can tell each of them are listed, but they are not readable so I am unable to differentiate which I should choose. I've tried closing out, putting all the information I'm looking up in the same workbook, etc. But nothing is working.

Bill says:
December 13, 2018 at 5:22 pm

Does your tool perform a vlookup on multiple rows with the same matching value? I tried the Merge Table feature, and it only reported the first instance/row. However, I have other rows that have the same matching value that I'm trying to add all together in the final result. Thank you.

Irina Goroshko (Ablebits Team) says:
December 14, 2018 at 3:41 pm

Hi, Bill,
Thank you for contacting us.
If I understood your task correctly, the Insert additional matching rows option on Step 6 of Merge Tables Wizard will do the job you need. Check this option and select where you want to get the additional matching rows inserted: at the end of the main table or after the rows with the same key value. Tick the chosen location and hit the Finish button. Please let me know if this helped!
Thank you.

Manish rao says:
November 29, 2018 at 5:14 am

Hi Abhijeet,
I am trying to Merge two worksheet using this add-on but it is showing an error message as follows.

'' The add-in can't add new records because it encounters Excel limit for the total number of rows in a sheet'
Thank you for using the Merge Two Tables tool! Please note that our products don't impose any additional limitations, so the number of rows and columns in the resulting table is defined by the version of Excel you have. Here you can find the limits for rows and columns.
If the number of rows in your sheet does not exceed the limit, please contact our support team at support@ablebits.com. Please attach your book and describe the Merge Two Tables steps you take in detail, mention all the options you tick in the wizard.I need some row according to implements column I put row selected numerical.

Irina Goroshko (Ablebits Team) says:
November 29, 2018 at 2:11 pm

Hi, Manish,
Thank you for using Merge Two Tables and for your comment. I am sorry you are experiencing difficulties with the tool.
As mentioned in the fragment you quote, Merge Two Tables does not impose any additional limitations, so the number of rows and columns in the resulting table is defined by the version of Excel you have. The possible solution is to turn off the backup option or not to select to add additional rows or columns. If this won’t help, please send your book to support@ablebits.com. Please describe the problem in detail, list all the steps you take in the wizard, and mention all the options you check. We'll do our best to help you.
Thank you.

Derrick Strom says:
October 18, 2018 at 3:11 pm

i don't seem to be able to get insert additional matching rows to work. I've tried both ways and both times they update the main table row data - I was expecting that it would add a row of new data with a status of matching or add a row of new data right after the matching row with a status of matching so I could compare the data in the fields I'm updating. I have 75k records in the main table and 27k in the lookup with 101 matches - I want to compare the names and company values for the 101 while adding the remaining 26,899 records to the main table with the names and company data in the correct fields for importing my records (the adding the new records (as new row) is working correctly and as expected.

Irina Goroshko (Ablebits Team) says:
October 18, 2018 at 3:35 pm

Hi, Derrick,
Thank you for your comment! And thank you for using Merge Tables Wizard, I am sorry you are experiencing problems with the tool. In order we could provide you with the most accurate feedback, please contact our support team at support@ablebits.com. Please attach your Excel workbook and describe all the options you tick in the wizard on each step, plus the result you expect to get and the result you actually get.
Thank you.

Antonio Casas says:
October 15, 2018 at 11:41 pm

I thought I had this problem solved but is doing it again. It goes from step 3 directly to step 6. I don't get the option to select the columns I want updated in the main table or pick the columns I want added to the main table. I'm doing all the work within a spreadsheet that has 6 tabs. I'm using Excel 2010.

Please Help,

Thanks

Irina Goroshko (Ablebits Team) says:
October 16, 2018 at 12:38 pm

Hello, Antonio,
Thank you for your interest in Merge Two Tables, I hope we will solve the problem and you will enjoy it!

As the tool skips steps 4 and 5, the reason why it behaves in such a strange way could be that on step 3 you select all the columns of your table as the matching ones. The thing is that the same columns cannot be matching, updated, or added, so if you select all the columns as matching, nothing is left to be updated and added, and the tool brings you right to the final step. If this is true, you probably have seen a message that you have selected all columns as matching and if you want to update some columns, please select fewer matching columns.
Please try out the add-in once again choosing only some matching columns on step 3 (not all the columns of your table) and let me know if this helped. If the problem still exists, please contact me again!

Thank you.

Antonio Casas says:
October 15, 2018 at 9:07 pm

Never Mind.
Please ignore my previous e-mail claiming that Steps 4 and 5 were not being executed. I closed all sessions of XCEL, restarted the process and it worked just fine.

A.Casas

Antonio Casas says:
October 15, 2018 at 8:36 pm

I decided to take the 7 Day Trial offer for the Merge Table Wizard. However, it only worked the first time. Thereafter, it skips steps 4 of 6 and 5 of 6. I wanted to test with it some more before I buy it. Please advise.

Thanks,

A. Casas

Abhijeet Tiwari says:
August 13, 2018 at 1:55 pm

Hello,

I am trying to Merge two worksheet using this add-on but it is showing an error message as follows.

'' The add-in can't add new records because it encounter Excel limit for the total number of rows in a sheet''

Kindly help me to solve this.
Thank you

Irina Goroshko (Ablebits Team) says:
August 13, 2018 at 2:35 pm

Hi Abhijeet,
Thank you for using the Merge Two Tables tool! Please note that our products don't impose any additional limitations, so the number of rows and columns in the resulting table is defined by the version of Excel you have. Here you can find the limits for rows and columns.
If the number of rows in your sheet does not exceed the limit, please contact our support team at support@ablebits.com. Please attach your book and describe the Merge Two Tables steps you take in detail, mention all the options you tick in the wizard.

Mathew D Wolf says:
June 25, 2018 at 4:09 pm

Hello, I am using the Merge Two Tables application and I am a trying to get every instance to merge but only the first instance is merging and leaving he rest of the merge fields blank. Please assist me.

Irina Goroshko (Ablebits Team) says:
June 26, 2018 at 9:02 am

Hi Mathew,
Thank you for using the Merge Two Tables tool! Please contact our support team at support@ablebits.com and describe your task in detail. It would be perfect if you could also attach your workbook, the result you get with Merge Two Tables on a separate sheet, and a sample of the result you would like to get. This will help us a lot to understand the problem and assist you.

Jim Hargis says:
June 21, 2018 at 7:00 am

I want ONLY the Merge Two Worksheets wizard, but you charge $100 for the entire Ultimate Suite, which I do NOT want. You were offering the Merge Tables tool for $60, what happened to it?

ACC says:
May 25, 2018 at 11:04 am

Hi,

My company is interested in buying the product, yet we wanted to solve a doubt first:
Can it combine multiple workbooks with multiple sheets to update a "master" workbook, but giving the option to choose which information we want to preserve? Or do you have another product that could do it?

Thanks.

ACC

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.