HomeGoogle DocsSpreadsheetMerge Two Tables in Google Sheets - The Ultimate Guide

Merge Two Tables in Google Sheets – The Ultimate Guide

Published on

No script, no add-on! I am back with another Google Spreadsheets tutorial. It’s about how to merge two tables in Google Sheets using formulas.

This tutorial may be a little lengthier as I have included different types of merging of tables. Yes! I am going to take into consideration different aspects of merging of tables. What are they?

The types and purpose of the merging of two tables may be different for you. In this tutorial, I have included 3 different types of merging of tables. Hope one of them will come to your use.

Note: Open this functions guide in a new tab as you can quickly refer to any functions that I am going to mention below.

Different Types of Merging of Two Tables in Google Sheets

Type 1:

A couple of years back, I used to merge tables in Excel (yes! that time I was using Excel) as part of our quarter ending financial statements.

You May Like: Google Sheets vs Excel – Comparison of a few functions in both.

I had been presented monthly salary reports of employees on a quarterly basis. It was related to the quarter ending so there were three reports, you can say three tables.

I usually merged those tables into one to make a three months summary report of employee salary.

Merge Two Tables in Google Sheets - Type 1

In the above example, I have merged two tables. It was like merging the duplicates in the first two columns and then adding the net salary (third column). The unique IDs are also added to the merged table as a new row.

I will give you the formula and step-by-step instructions on how to merge two tables as above in Google Sheets. Of course, the solution will be formula oriented.

Type 2:

Sometimes you may want to merge two tables differently as below.

Of course, here again, you want to merge the duplicates. But instead of adding their salary amount (see the above screenshot), you may want to place that in a new column as below.

Let me take your attention to the last two columns of the merged table.

Merging of Two Tables in Docs Sheets - Type 2

I hope the above table (merged) can well explain what I meant. See Type 3 below and then we can go to the formulas.

Type 3:

This is an ultimate guide on how to merge two tables in Google Sheets. So I thought of including all the possible variations.

Below I am merging two tables in a unique way! It’s by searching the column header and then combining the matching columns.

This time, for the example, I am using a sample sales data, not the employee salaries.

Search header row and combine two tables in Google Sheets

How to Merge Two Tables in Google Sheets

I am going to detail the above three types of merging of datasets below. I know it will be time taking for you to make the demo sheet. So I am sharing my demo sheet with you.

Example Sheet [Merging of Tables]

This file is set to copy mode. If there is no domain level restriction to receive an outside file, you can successfully copy it.

If you can’t copy, leave it. There are only a few lines of sample data to type. So here we go.

Merge Data Based on Unique IDs and Add Amount Column (Type 1)

Here I am using the function Query (please don’t let the function name to scare you) to merge two tables in Google Sheets. The same formula you can use to merge more than two tables also.

Table 1:

Type 1: Table 1

Table 2:

Type 1: Table 2

Check these two tables. I am merging the first two columns and then adding their values in the last column.

=query({A2:C;E3:G},"Select Col1, Col2,sum(Col3) where Col1 is not null group by Col1,Col2",1)

This Query in cell I2 will merge the two tables as below.

Type 1: Output

Suppose you may want to merge one more table. You can use the third table as below.

This is a generic formula. Replace table 1, table 2 and table 3 with corresponding data ranges.

=query({table 1;table 2;table 3},"Select Col1, Col2,sum(Col3) where Col1 is not null group by Col1,Col2",1)

Merge Data Based on Unique IDs and Insert Amount Column (Type 2)

In this example, I am going to use the same above table 1 and table 2. Here the formula is entirely different.

Here I am merging the first two columns which contain name and ID then placing the third column side by side.

I have three formulas. The first one is in cell I3 which returns the values in I3:J8.

Type 2: Formula Output
=unique({A3:B7;E3:F7})

The Unique based formula combines the first two columns in table 1 and table 2 and removes the duplicates. So we have the unique Employee Codes and Names.

The second formula in cell K3 returns the values in K3:K7.

=ArrayFormula(IFERROR(vlookup(I3:I8,A3:C7,3,0),0))

What does this formula do?

This is a Vlookup array formula, which uses multiple search keys, returns multiple values.

=ArrayFormula(iferror(vlookup(I3:I8,E3:G7,3,0),0))

New to Vlookup in Array use? Do check this tutorial – How to Use Vlookup to Return An Array Result in Google Sheets.

The search keys are the unique user IDs in I3:I8 and lookup table is table 1 (A3:C7). The output column is column 3 (Amount) in the lookup table.

In cell L3, I have used one more Vlookup formula. This time the lookup table is table 2 (E3:G7). There are no other differences.

=ArrayFormula(iferror(vlookup(I3:I8,E3:G7,3,0),0))

Search Header and Combine Matching Columns (Type 3)

This a totally different approach compared to the above two types of table merging in Google Sheets.

Here actually I am combing two tables not merging. But you can later use that combined data and merge using the Type 1 method above. I’ll detail that under a separate title below.

If you have two datasets with similar columns and want to combine them, you can follow this method.

I think I must detail it with screenshots.

Table 1:

Type 3: Table 1

Table 2:

Type 3: Table 2

I have two sales reports. The header row of the first table contains the labels – “Month”, “Sales Person”, and “Sales Volume”. These are column names or we can say field titles.

Related: Column Heading | Column Label | Column Name | Field | Field Label in Google Sheets.

I want to search these labels in table 2 and if found combine that columns under table 1.

So the formula must start with;

{A1:C4;

Because we want the first table as it is in the output. The final formula is;

=iferror({A1:C4;ArrayFormula(hlookup(A1:C1,E1:F4,row(A2:A),0))})

I have used Hlookup to search across the first row in table 2 for the filed labels in A1:C1 (search key range).

The range A1:C1 is the header row of table 1 which is the search key in Hlookup. The Hlookup array formula will return the matching columns under A1:C4.

Type 3: Output

Our topic is how to merge two tables in Google Sheets. In that context does the type 3 example justify to the topic?

I think it justifies partially. Actually what I have shown is how to search column names and combine matching columns.

In the following example, I am going to show you how to merge similar rows in that combined data. You can use it as a full-fledged data consolidation method in Google Docs Sheets.

Actually this is a combination of type 1 and type 3 data merging.

How to Combine and Merge Matching Columns in Two Tables in Google Sheets

To simplify the formula, I am using helper tables here. I will tell you what’s that. Before familiar with the sample data as it’s new.

Table 1 Sample Data:

Sales quantity of 3 items for the month of January, February, and March – Sales Executive 1.

combine and merge - table 1

Table 2 Sample Data:

Sales quantity of 3 items for the month of January, March, and April – Sales Executive 2.

combine and merge - table 2

Both the tables have matching and mismatching columns. Take a look at the column names (month names) to understand that.

How to properly merge these two tables in Google Sheets? Here are the step-by-step instructions.

To search column names and combine two tables we can use the Hlookup function.

Step # 1:

In Hlookup, we must use the unique column names of table 1 and 2 as the search keys. This we can generate using the below formula.

=transpose(unique(transpose({A1:D1,F1:I1})))

In order to understand what this formula returns enter it in cell K1. Do make sure that K1:O1 is blank. Keep this formula there.

Step # 2:

We have now the search keys for Hlookup. Now we want the lookup range. It’s the table 1 range A1:D.

Use the below Hlookup array formula in cell K2.

=ArrayFormula(IFERROR(hlookup(K1:P1,A1:D,row(A2:A),0)))

It generates a helper table as below.

helper table 1

Step # 3:

Use the above same formula in cell Q2. Just change the range A1:D with the table 2 range F1:I.

=ArrayFormula(IFERROR(hlookup(K1:P1,A1:D,row(A2:A),0)))

That generates our helper table 2.

helper table 1 and 2

Now time to use Query to merge these two tables.

Step # 4:

Just enter this formula in cell W1 and voila!

=query({K1:O4;Q2:U4},"Select Col1, Sum(Col2),Sum(Col3),Sum(Col4),Sum(Col5) group by Col1",1)
Formula Output - Combine and Merge

That’s all. Enjoy!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

2 COMMENTS

  1. I am trying formula Type 1 in the same sheet supplied by the author. (linked below)

    It works in his example (Tab 1) but when I attempt it with my data (Tab 5) I am given the following error code; “Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col3”

    Can someone please help me troubleshoot this?

    — link removed by the admin —

    • Hi, Wade,

      You want to merge two tables and they are in the range A1:B and D1:E. Both the tables have now header rows.

      Here is your formula.

      =query({A1:B;D1:E},"Select Col1, Col2,sum(Col3) where Col1 is not null group by Col1,Col2",1)

      It should be as below.

      =query({A1:B;D1:E},"Select Col1,sum(Col2) where Col1 is not null group by Col1,Col2",0)

      Because you have only two columns in your table and you want to sum the second column.

LEAVE A REPLY

Please enter your comment!
Please enter your name here