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

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:

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:

Different number of columns in two tables:

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

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

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

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:

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:

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.

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.

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.

Then the main table is updated as below screenshots shown.

The same number of columns in two tables:

Different number of columns in two tables:

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:

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

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

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

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:

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:

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:

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:

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.

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

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

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

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:

2. Add duplicated matched rows:

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.

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.

3. Add a status column:

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.

4. Update options:

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.

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.

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.

Demo: update or merge tables by matching column

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Download the free trial now!

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.