Merge two worksheets in Excel

Merge Two Tables is designed to quickly combine Excel worksheets by matching columns. The tables of interest can be located in the same workbook or in two separate Excel files and have different numbers of rows and columns.

With Merge Tables Wizard for Excel you can:

• Use one or several key columns as the lookup criteria.
• Add new columns to the main table or update the existing ones.
• Add non-matching rows to the end of the table after all other data.
• Pull additional matches from your lookup table.
• Color updated rows or mark the changes in the status column.
• Update only empty cells in your main table if you don't want to overwrite the existing values.
• Update only if cells from lookup table contain data.

You can use Vlookup or Index/Match formulas to merge two worksheets by a column. If your knowledge of Excel formulas is above average, you can combine a few different functions in an array formula to return multiple matches. Probably, you could even figure out a way to vlookup with multiple criteria by concatenating several lookup values in a helper column.

Or, you can use our Merge Tables Wizard to combine two Excel worksheets by any number of key columns in six easy steps.

Click the Merge Two Tables button on the Ablebits Data tab, and do the following:

1. Select your main table.
2. Select your lookup table.
3. Choose the key column(s) to match.
4. Specify the columns to update in your main table.
5. Optionally, choose the columns to add to your main table.
6. Select additional merging options if needed, and click Finish.

See a more detailed description here.

Just tick off the Case-sensitive matching option on step 3. The wizard will treat lowercase or uppercase characters in the key columns as different values.

Of course. On step 4, don't select any columns to update, simply click Next. On steps 5, choose the columns to add to your main table.

If you want to copy such rows to your main table, select the Add non-matching rows to the end of the main table checkbox on step 6. Obviously, if you don't want to transfer any non-matching rows, then don't select this option :)

By default, the Merge Tables Wizard takes the first matching row it finds. If you wish to pull all matches to your main table, on step 6, select the Insert additional matching rows box, and then choose either to add them all to the end of your table or to insert right after the row with the same key value.

No worries, we've got you covered. On step 6, select one of the below options, or both:

• To ignore empty cells in the lookup table, tick off Update only if cells from the lookup table contain data.
• Not to overwrite any existing values in your main table, select Update only empty and new cells in the main table.

There are two ways to identify the changes: text and color. If you prefer textual marks, add the Status column to your main table, and then you can filter updated rows, non-updated rows, or new rows. If you like coloring better, shade the updated cells and/or added rows with any color of your choosing. Of course, nothing prevents you from using both options at a time :)