How To Merge Two Tables In Excel

How to merge two tables by matching a column in Excel?

Supposing you accept ii tables in two unlike sheets, one is master table, and the other is new data tabular array. Now you lot want to merge these two tables by a matching column and update the information as below screenshot shown, how tin can you quickly solve it in Excel? In this article, I will introduce the tricks on combining ii tables by a column rapidly.

doc merge tables by a column 1

Merge 2 tables by a cavalcade with VLOOKUP

To merge two tables by a cavalcade matching, you can use VLOOUP function.

i. Select a blank prison cell next to the chief table yous, C2 for instance. See screenshot:
doc merge tables by a column 2

2. Enter this formula =IFERROR(VLOOKUP($A2,Sheet2!$A$i:$C$six,Cavalcade(A1),Fake),"") into it, and so drag the autofill handle right until blank cell appears, and then drag the make full handle down to the cells needed this formula. See screenshot:
doc merge tables by a column 3

three. You tin can add the new cavalcade headers to the merged table. Meet screenshot:
doc merge tables by a column 4

Merge ii tables past a column with Tables Merge function

The LOOKUP function will be lilliputian troublesome for yous, but fortunately, there is a helpful part Tables Merge in Kutools for Excel which can quickly add together and update new items in the main table based on another tabular array.

1. Select the primary table y'all want to use to update and merge with another, click Kutools Plus > Tables Merge.
doc table merge 1

2. Then in the Tables Merge wizard dialog, select the lookup tabular array in Select the lookup table textbox, bank check the options if the tables have headers.
doc table merge 2

3. Click Next, in step ii of the wizard, check the fundamental column you want to merge based on.
doc table merge 3

four. Click Next, in pace 3 of magician, check the column(southward) you lot want to update the information.
doc table merge 4

5. Click Adjacent to side by side step, check the new column(s) y'all want to add to the chief data.
doc table merge 5

6. Click Next, in the concluding footstep, you demand to set some options for the new items, such as highlighted the updated ones with background color.
doc table merge 6

vii. Click Finish. The main table has been updated and added with new items.
doc table merge 7

Merge multiple tables by column headers into one sheet with Kutools for Excel

If you want to merge multiple tables beyond sheets or workbooks by column headers into a single canvass as below screenshot shown, y'all can employ the powerful Combine function of Kutools for Excel to chop-chop solve information technology.
doc merge tables by a column 5

Later on installing Kutools for Excel, delight practise as beneath:( Gratuitous Download Kutools for Excel Now!)

i. Enable Excel and click Kutools Plus > Combine, and in the popping dialog, check Combine multiple worksheets from workbooks into one worksheet. Encounter screenshot:
doc kutools combine 1
doc kutools combine 2

2. Then click Next to go to Stride 2 of 3 dialog, and add the workbooks by clicking Add together button, so select the sheet names you want to merge from Worksheet list section. See screenshot:
doc kutools combine 3

iii. Click Next to get to Step 3 of 3 dialog. In the concluding step of the dialog, specify the title row number into it, here is 1, likewise you can specify other setting options as yous demand. Meet screenshot:
doc kutools combine 4

4. Click Terminate, a dialog pops out for choosing a folder to place the merged new workbook. And click Save to go to offset 1 Kutools for Excel dialog which remind you whether open the combined workbook, click Yes to open it or no, so the 2nd Kutools for Excel dialog pops out for reminding you lot to salve this combine scenario, click whatever options as you demand.
doc merge tables by a column 10

At present the sheets have been merged into one single sheet based on column headers.
doc merge tables by a column 13

The Best Function Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by fourscore%

Reuse: Apace insert complex formulas, charts and annihilation that you have used before; Encrypt Cells with password; Create Mailing List and transport emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Dissever Cells Content; Combine Indistinguishable Rows/Columns... Forbid Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Notice and Fuzzy Detect in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more than...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by calendar month/week/solar day, frequency and more; Special Filter by assuming, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Carve up Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Function/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or arrangement. Full features 30-twenty-four hour period costless trial. sixty-day coin back guarantee.

kte tab 201905

Part Tab Brings Tabbed interface to Part, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity past l%, and reduces hundreds of mouse clicks for you every twenty-four hours!

officetab bottom