Combine Two Tables with Unequal Rows Horizontally in Google Sheets

Using the HSTACK function, we can combine two tables or two Query formula results with unequal rows horizontally in Google Sheets.

I can explain it in simple terms.

I have two tables. The first table consists of three lines (C1:D3), while the second table consists of four lines (F1:G4).

Here I am combining the above two tables with unequal numbers of rows horizontally in Google Sheets.

This is our usual approach with Curly Brackets, which would return an error #REF.

={C1:D3,F1:G4}

The tooltip will remind you that function ARRAY_ROW parameter 2 has a mismatched row size. Expected: 3. Actual: 4.

How to overcome this? How do I properly combine two tables with unequal numbers of rows horizontally in Google Sheets?

Here is how to combine two tables with unequal numbers of rows horizontally.

Combine Two Tables Having Different Numbers of Rows Horizontally

Below you can find two types of formulas, and feel free to skip the old-school approach.

Old School Approach (Two Physical Tables)

The two physical tables to combine are in the C1:D3 and F1:G4 ranges/arrays.

Here are the steps to horizontally combine them in Google Sheets.

Steps

First of all, generate 100 rows with error values. We can use the Sequence function to get those error value rows.

=ArrayFormula(sequence(100,2)/0)

The number 100 represents a hundred rows, and the number 2 represents two columns. Remember, we have two columns in each table.

Let’s add these 100 rows of error values below the tables. Before that, we should make the #DIV/0 errors blanks. How?

Wrap the above Sequence formula with IFERROR. Here is the table after adding 100 blank rows below.

Table # 1:

={C1:D3;iferror(ArrayFormula(sequence(100,2)/0))}

Table # 2:

={F1:G4;iferror(ArrayFormula(sequence(100,2)/0))}

Now let’s constrain the rows in each table to 10 using Array_Constrain. Here are those formulas.

Table # 1:

=array_constrain({C1:D3;iferror(ArrayFormula(sequence(100,2)/0))},10,2)

Table # 2:

=ARRAY_CONSTRAIN({F1:G4;iferror(ArrayFormula(sequence(100,2)/0))},10,2)

Now we can combine these two tables horizontally as both tables have ten rows each.

={
array_constrain({C1:D3;iferror(ArrayFormula(sequence(100,2)/0))},10,2),
array_constrain({F1:G4;iferror(ArrayFormula(sequence(100,2)/0))},10,2)
}

Let’s just fine-tune the formula.

FORMULA 1

=ArrayFormula(
     {
     array_constrain({C1:D3;iferror(sequence(100,2)/0)},10,2),
     array_constrain({F1:G4;iferror(sequence(100,2)/0)},10,2)
     }
)

Combining two physical tables with unequal numbers of rows

This way we can combine two physical tables having unequal numbers of rows horizontally in Google Sheets.

Note

When you combine tables horizontally, the number of columns won’t make any issue. But when generating error rows, do remember to use Sequence formulas accordingly.

For example, if Table 1 has two columns and Table 2 has five columns, use the Sequence formulas as follows.

Table # 1:

=ArrayFormula(sequence(100,2)/0)

Table # 2:

=ArrayFormula(sequence(100,5)/0)

There are no other changes in the formula.

New Approach (Two Physical Tables): Recommended ✔

With the help of HSTACK, we can combine two or more tables having mismatching rows horizontally.

In the above example, we can use the following HSTACK formula.

It adds #N/A cells to match the number of rows. The IFERROR removes them.

=IFERROR(HSTACK(C1:D3,F1:G4))

Combine Two Query Results with Unequal Numbers of Rows Horizontally

Old School Approach (Two Virtual Tables)

The real purpose of this tutorial is not to help you combine two physical tables with different numbers of rows horizontally. Then?

We want to combine two Query formula results with unequal numbers of rows horizontally.

I know the above example may help you learn the logic. Now things are easy for you.

You want to change the two table references in the above formula (FORMULA 1), i.e., C1:D3 and F1:G4, with two Query formulas.

Sample Data: Two Tables and Two Summaries

G1 Formula:

=query(A1:B,"Select A, sum(B) where A is not null group by A",1)

J1 Formula:

=QUERY(D1:E,"Select D, sum(E) where D is not null group by D")

Sample data and query formulas as tables

Let’s consider the above two Query formula results in our test as two tables. We are unsure whether the Query formula results have an equal number of rows.

Here we can follow the same approach which we have chosen to combine two physical tables with unequal numbers of rows horizontally.

In that formula, replace C1:D3 with the G1 formula and F1:G4 with the J1 formula. It will be as follows.

=ArrayFormula(
     {array_constrain(
        {query(A1:B,"Select A, sum(B) where A is not null group by A",1);
        iferror(sequence(100,2)/0)},10,2),
     array_constrain(
        {QUERY(D1:E,"Select D, sum(E) where D is not null group by D");
        iferror(sequence(100,2)/0)},10,2)}
)

Combine two Query functions with unequal numbers of rows

New Approach (Two Virtual Tables): Recommended ✔

It is pretty straightforward. Enter the two Query formulas within HSTACK as comma-separated and wrap with IFERROR.

That’s it.

=IFERROR(
     HSTACK(
        QUERY(A1:B,"Select A, sum(B) where A is not null group by A",1),     
        QUERY(D1:E,"Select D, sum(E) where D is not null group by D")
     )
)

Example_Sheet_9720