3 Formulas to Join Tables in Google Sheets

Managing and merging datasets in Google Sheets can be time-consuming, especially when dealing with multiple tables. The VSTACK and HSTACK functions provide a simple yet powerful way to stack data dynamically, eliminating the need for manual copying or complex array formulas. 

In this tutorial, we’ll explore how these functions work, compare them to SQL’s UNION ALL, and demonstrate practical use cases to improve your spreadsheet workflow.

Check out the video-tutorial:

Task

Let’s imagine – we have three tables with different sets of data from different sources on the same Google Sheets tab.

Luckily, when we have to pull data from different sources, we don’t do it manually – we use Google Sheets add-ons that do this instead of us. For example, Supermetrics or Windsor.ai – these two never disappoint you when it comes to the variety of sources to pull data from. 

If your intention is to pull up the data to Looker Studio, you need to have a monolit table containing the data from all the sources, like these two:

As you may have noticed, the data is the same, but the ways to create them vary.

Merging filters and data ranges

Here is the full formula that helped me to create the table on your right:

={filter(A3:D7, len(A3:A7));filter(F3:I7, len(F3:F7));filter(K3:N7, len(K3:K7))} 

It may seem to be difficult, but in fact it’s not, so let’s decompose it.

={filter(A3:D7, len(A3:A7)) – we selected the data range from A3 to D7 and used the length of the first column of the table.

NOTE that if you want to include all the rows from the tab you may not narrow it down to a certain cell, just leave the letter of a column, for instance: A3:D. This is an essential thing to know, when your data updates daily and you don’t know how many rows there are in the table. 

Then, we apply the same formula to other tables followingly uniting all the formulas using these signs { } in the beginning and the end. 

This formula is very useful for countless rows, as it filters out the empty rows and pulls up only the rows with data.

VSTACK formula

For the same purpose we can use the VSTACK formula. This one automatically lets us select the data ranges (in this case A3:D7, I3:F7, K3:N7). Very easy and very fast. However, this formula has a disadvantage – if there are any empty rows in the date range, the formula will transfer them to the new table you’re creating. 

When working with Looker Studio, empty rows will cause errors and broken charts, so that’s the thing you definitely have to avoid. 

As a result, I can suggest using this formula when you know the exact number of rows, so that you could control the blank rows. 

HSTACK formula

This formula is built exactly the same way as the VSTACK formula:

=HSTACK([range1],[range2]…])

But, in contrast to the VSTACK formula that places the selected data ranges one below another, the HSTACK formula is placing them horizontally, one beside another.

Summary 

The VSTACK and HSTACK functions in Google Sheets are powerful tools for efficiently combining data. VSTACK allows you to stack data vertically, creating a continuous column, while HSTACK arranges data side by side in a single row. In addition, you can use filters for the same purpose.

Hope you liked this article! If you found it interesting – let me know what you think about the formulas in the comments section!