Merging Two Tables in Google Sheets: The Ultimate Guide

Published on

Merging two tables in Google Sheets is a broad topic. Generally, it involves combining data from two separate tables into a single table. There are two main ways to achieve this:

Stacking Vertically: This method is used when both tables have similar column headers (e.g., months, weeks, categories). Stacking works even if the second table has fewer or more columns.

Joining Tables: This is a more controlled way to merge tables that share a common column (unique identifier). It is a broad topic that involves left, right, full, and inner joins. You should also know how to handle duplicate unique identifiers (IDs).

In this tutorial, we’ll focus on stacking vertically and a basic approach using a lookup formula to merge (join) tables based on a unique identifier.

For detailed tutorials on various join types and advanced merging techniques, check out the ‘Resources’ section below.

Merging Two Tables by Vertical Stacking in Google Sheets

In this example, the first table in A1:C4 contains three columns: Month, Sales Person, and Sales Volume, while the second table in E1:H5 contains four columns: Month, Sales Person, Location, and Sales Volume.

Merging two tables by vertical stacking in Google Sheets

So, the number of columns is not the same. Also, the number of rows in the first table is 4, and the second table has 5 rows. How do we properly stack these tables vertically?

This type of merging is easier using HLOOKUP together with some other functions. Here are the step-by-step instructions.

Step 1: Merge Headers

To merge headers, you should first stack them horizontally and apply the UNIQUE function. Enter the following formula in cell E7:

=UNIQUE(HSTACK(A1:C1, E1:H1), TRUE)

The HSTACK function stacks the headers horizontally, and the UNIQUE function returns the distinct headers.

Step 2: Search Headers in Table #1 and Return Matching Rows

In cell E8, use this HLOOKUP formula:

=ArrayFormula(HLOOKUP(E7:H7, A1:C4, SEQUENCE(ROWS(A1:C4)-1, 1, 2), 0))

This follows the syntax HLOOKUP(search_key, range, index, [is_sorted]) where:

  • search_key: E7:H7 (distinct headers)
  • range: A1:C4 (table #1)
  • index: SEQUENCE(ROWS(A1:C4)-1, 1, 2) (the row indices, dynamically generated)
  • is_sorted: 0 (exact match)

We use ARRAYFORMULA because we have multiple search keys to look up.

Now, we need to merge the second table with the output of this HLOOKUP, which follows the same approach as above.

Step 3: Search Headers in Table #2 and Return Matching Rows

In cell E11, insert the following formula:

=ArrayFormula(HLOOKUP(E7:H7, E1:H5, SEQUENCE(ROWS(E1:H5)-1, 1, 2), 0))

It’s the same formula as in step 2, but with the table reference and row index changed.

Final Step: Combining Formulas

Combine the formulas into one to create a dynamic merge.

You can enter the following formula directly into cell E7. Before doing so, remove the step 1 – 3 formulas from the cells.

=ArrayFormula(LET(
   table_1, A1:C4, 
   table_2, E1:H5, 
   header, 
      UNIQUE(HSTACK(
         CHOOSEROWS(table_1, 1), 
         CHOOSEROWS(table_2, 1)
      ), TRUE), 
   IFNA(VSTACK(
      header, 
      HLOOKUP(header, table_1, SEQUENCE(ROWS(table_1)-1, 1, 2), 0), 
      HLOOKUP(header, table_2, SEQUENCE(ROWS(table_2)-1, 1, 2), 0)
   ))
))

In this formula, we use the LET function to name the tables and use those names in subsequent value expressions and formula expressions.

This approach ensures the merging is dynamic and handles tables with different numbers of columns and rows.

You just need to replace the table references A1:C4 and E1:H5 with the table references in your sheet. The formula handles the rest.

Merging Two Tables Using a Matching ID in Google Sheets

This is one of the most common types of merging.

Imagine you maintain separate tables for each month’s salaries of your employees. You might want to generate a report that shows their monthly salaries in one consolidated table.

Merging two tables based on IDs in Google Sheets

This involves merging two tables by stacking them horizontally. This method uses unique IDs (such as employee IDs) for merging.

Step 1: Merge Common Columns Including IDs

Based on our sample data, we aim to stack the salaries horizontally while matching the IDs. Aside from IDs, the common column in both tables is the name column.

To merge these columns from both tables, start by inserting the following formula into cell E9:

=UNIQUE(VSTACK(A2:B6, E2:F6))

The VSTACK function vertically stacks the IDs and Names from both tables, and the UNIQUE function returns distinct rows. This effectively merges the IDs and Names from both tables into one.

Step 2: Search Distinct IDs in Table #1 and Return Matching Columns

Insert the following VLOOKUP formula into cell G9 to retrieve the salaries from Table #1:

=ArrayFormula(IFNA(VLOOKUP(E9:E14, A1:C6, 3, 0), 0))

This follows the syntax VLOOKUP(search_key, range, index, [is_sorted]) where:

  • search_key: E9:E14 (distinct IDs)
  • range: A1:C6 (table #1)
  • index: 3 (the column to return from table #1)
  • is_sorted: 0 (exact match)

The IFNA function returns 0 if any ID doesn’t match in table #1.

Step 3: Search Distinct IDs in Table #2 and Return Matching Columns

Similar to step #2, use the following VLOOKUP formula in cell H9:

=ArrayFormula(IFNA(VLOOKUP(E9:E14, E1:G6, 3, 0), 0))

This method allows us to merge two tables by matching IDs in Google Sheets.

Resources

We have explored two methods to merge tables in Google Sheets. For advanced merging techniques, please refer to these guides:

Prashanth KV
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.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.