Skip to main content

Easily update or merge tables by matching column from two worksheets/workbooks in Excel

Author: Siluvia Last Modified: 2024-12-11

Kutools for Excel

Boosts Excel With 300+
Powerful Features

For merging two tables or updating a table based on the new data in another table in Excel, the first method popped into your mind may be copying and pasting the data back and forth. Furthermore, the Vlookup function can also help to merge tables by matching data in a column. However, these two methods are time-consuming and annoying when operating in your work. With the Tables Merge utility of Kutools for Excel, you can quickly update or merge two tables by a matching column from two worksheets or workbooks.

Update table with new data in another table by matching column

Merge tables with adding new data column by matching column


Click Kutools Plus > Tables Merge. See screenshot:

shot tables merge 01


Update table with new data in another table by matching column

Supposing you have tables as below screenshots shown, how to update the main table with new data in lookup table by the Product column? Please do as follows.

The same number of columns in two tables:

shot tables merge 02

Different number of columns in two tables:

shot tables merge 03

1. Click Kutools Plus > Tables Merge. See screenshot:

shot tables merge 01

2. In the Tables Merge – Step 1 of 5 dialog box, click the shot tables merge 04 button in the Select the main table section to select the table you will update with new data in another table. See screenshot:

shot tables merge 05

3. Then click the shot tables merge 04 button in the Select the lookup table section to select the table you will lookup values from.

shot tables merge 06

Notes:

1. If the lookup table locates in another sheet of current workbook, just shift to that sheet by clicking on the sheet tab and then select the table.

2. If the lookup table locates in a different workbook, please click that workbook name in the left Workbook & Sheet pane to open it and then select the table (For displaying the book name in the Workbook & Sheet pane, you need to open the workbook in advanced). See screenshot:

shot tables merge 07

3. Click the Next button in the Tables Merge – Step 1 of 5 dialog box.

4. In the Tables Merge – Step 2 of 5 dialog box, select the matching column you will update table based on, check the Case sensitive box as you need, and then click the Next button. See screenshot:

shot tables merge 08

Note: If you check Case sensitive option in the dialog box, the updated and lookup data will be identified case sensitive.

5. Then the Tables Merge – Step 3 of 5 dialog box open, check the column that you want to update with new data, and then click the Next button.

shot tables merge 00

6. If the vlookup table has more columns than the main table, the dialog box Table Merge – Step 4 of 5 will open. Please select the column to add in the main table and then click the Next button.

If there are same number of columns in the two selected tables, you will shift to the step 8 directly.

shot tables merge 09

7. In the Tables Merge – Step 5 of 5 dialog box, please specify the settings options based on your needs, and then click the Finish button. See screenshot.

shot tables merge 10

Then the main table is updated as below screenshots shown.

The same number of columns in two tables:

shot tables merge 02

Different number of columns in two tables:

shot tables merge 03

Please go to the Notes section to get more details of the setting options configuration.


Merge tables with adding new data column by matching column

This section will show you how to merge two tables with adding new data column by matching column without changing the existing data in the main table. See screenshot:

shot tables merge 11

1. Click Kutools Plus > Tables Merge. See screenshot:

shot tables merge 01

2. In the Tables Merge – Step 1 of 5 dialog box, click the shot tables merge 4 button in the Select the main table section to select the table you will update with new data in another table. See screenshot:

shot tables merge 05

3. Then click the shot tables merge 4 button in the Select the lookup table section to select the table you will lookup values from.

shot tables merge 06

Notes:

1. If the lookup table locates in another sheet of current workbook, just shift to that sheet by clicking on the sheet tab and then select the table.

2. If the lookup table locates in a different workbook, please click that workbook name in the left Workbook & Sheet pane to open it and then select the table (For displaying the book name in the Workbook & Sheet pane, you need to open the workbook in advanced). See screenshot:

shot tables merge 07

4. Click the Next button in the Tables Merge – Step 1 of 5 dialog box.

5. In the Tables Merge – Step 2 of 5 dialog box, select the matching column you will update table based on, check the Case sensitive box as you need, and then click the Next button. See screenshot:

shot tables merge 08

Note: If you check Case sensitive option in the dialog box, the updated and lookup data will be identified case sensitive.

6. In the Tables Merge – Step 3 of 5 dialog box, click the Next button directly without selecting any column in the Please select the columns to update the main table box, see screenshot:

shot tables merge 12

7. In the Tables Merge – Step 4 of 5 dialog box, check the Main table columns checkbox to select all columns in the lookup table you will merge to the main table, and then click the Next button. See screenshot:

shot tables merge 13

8. In the Tables Merge – Step 5 of 5 dialog box, please specify the settings options based on your needs, and then click the Finish button. See screenshot.

shot tables merge 10

Then the two selected tables are merged as below screenshots shown.

shot tables merge 11

Notes: In the Setting Options dialog box, you can configure the settings as follows:

1. Add mismatched rows to the end of main table:

shot tables merge 14

If you check this option, all unmatched values in the lookup table will be added at the end of the main table as following screenshot shown:

shot tables merge 15

2. Add duplicated matched rows:

shot tables merge 16

1.1 If there are duplicated matching rows in two selected tables, select the Add the duplicated matching rows to the end of the main table option will add the duplicated matching rows at the end of the main table as below screenshot shown.

shot tables merge 17

1.2 Select the Insert the duplicated matching rows after the rows with the same value option will insert the duplicated matching rows below the same value in the main table as below screenshot shown.

shot tables merge 18

3. Add a status column:

shot tables merge 19

You can mark all updated cells by adding a status column to the right of the main table with checking the Add a status column box in the Add Options section.

shot tables merge 20

4. Update options:

shot tables merge 21

4.1 If blank cells are populating in the lookup table, to only update the main table with existing data but ignoring the blank cells in lookup table please select the Only update the cells when there is data in the lookup table option. You will get the updated result as below screenshot shown.

shot tables merge 22

4.2 If blank cells are existing in the main table, to only update all blank cells and add unmatched data without changing the original data in the main table, please select the Only update the empty and new cells in the main table option. See the updated result of the main table as below screenshot shown.

shot tables merge 23

5. You can highlight all updated cells in the main table with specified background color and font color as you need in the Highlighting options section.

shot tables merge 24


Demo: update or merge tables by matching column

 
Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy permanently free AI features! Download Now!

Productivity Tools Recommended

Office Tab: Use handy tabs in Microsoft Office, just like Chrome, Firefox, and the new Edge browser. Easily switch between documents with tabs — no more cluttered windows. Know more...

Kutools for Outlook: Kutools for Outlook offers 100+ powerful features for Microsoft Outlook 2010–2024 (and later versions), as well as Microsoft 365, helping you simplify email management and boost productivity. Know more...


Kutools for Excel

Kutools for Excel offers 300+ advanced features to streamline your work in Excel 2010 – 2024 and Microsoft 365. The feature above is just one of many time-saving tools included.

🌍 Supports 40+ interface languages
✅ Trusted by 500,000+ users and 80,000+ businesses worldwide
🚀 Compatible with all modern Excel versions
🎁 30-day full-featured trial — no registration, no limitations
Kutools for Excel RibbonKutools for Excel Ribbon