In order to combine two tables with different number of columns in Query, you must know one basic thing. What’s that?

How to insert columns in tables that contain the value zero in each row. Do you know why?

We can only combine tables in Google Sheets that have the same number of columns. There is one more important thing.

Even if you combine two tables with different number of columns in Google Sheets Query, it may not return correct output.

To work a combined table in Query properly, you must ensure that you are combining a text column with another text column, a number column with another number column and so on.

Query won’t work correctly in a column contain mixed type data

Example:

Assume your Table 1 contains three columns (A1:C). In that column A contains Sales items, B and C contains monthly sales values for the month of January and February 2019 respectively.

Your Table 2 contains four columns (A1:D). In that Column A contains Sales items, B, C, and D contains the sales for the month of January, February, and March 2019.

How to combine these two tables with different number of columns in Query in Google Sheets.

How to Combine Two Tables with Different Number of Columns in Google Sheets Query

Step # 1: Add one virtual column to the table 1

In my earlier post, I have explained how to insert virtual columns in tables. Here is that Spreadsheet tutorial – How to Insert Blank Columns in Google Sheets Query.

In that tutorial under the title Insert Columns Filled With 0 Values in a Query you can see one formula that I am using here with minor modification.

Formula # 1:

=ArrayFormula(if(Len(Sheet1!A3:A),iferror({Sheet1!A3:C," "/row(Sheet1!A3:A)},0),))

I have used the LEN formula additionally to limit the values in the newly added column up to the values in A3:A.

Step # 2: Combine Table 2 with Table 1

Since Table 1 and Table 2 now contain an equal number of columns, you can combine them as below.

Generic Formula:

{table 1;table 2}

Here is that formula.

Formula # 2:

={ArrayFormula(if(Len(Sheet1!A3:A),iferror({Sheet1!A3:C," "/row(Sheet1!A3:A)},0),));Sheet2!A3:D}

The combined table will contain lots of blank rows between the two tables. With Query, we can remove such blank rows.

Formula # 3:

=Query({ArrayFormula(if(Len(Sheet1!A3:A),iferror({Sheet1!A3:C," "/row(Sheet1!A3:A)},0),));Sheet2!A3:D},"Select * where Col1<>''")

In Google Sheets, this way you can combine two tables with different number of columns in Query. Here is that combined output.

Combine and Consolidate Two Tables with Different Number of Columns in Google Sheets

Do you know how to do data consolidation in Google Sheets? Check this tutorial in your free tim – Consolidate Data from Multiple Sheets Using Formula in Google Sheets.

You have learned how to combine two data ranges with different number of columns. See how to consolidate that combined data.

Formula # 4:

=Query({ArrayFormula(if(Len(Sheet1!A3:A),iferror({Sheet1!A3:C," "/row(Sheet1!A3:A)},0),));Sheet2!A3:D},"Select Col1, Sum(Col2),Sum(Col3),Sum(Col4) where Col1<>'' group by Col1",0)

Don’t think that this formula is tough to follow. If you have properly understood how to combine two tables with different columns in Query, then things are easy. See the below screenshot that shows the formula # 4 and its output.

I have underlined the changes in this formula in comparison with the formula # 3 that combines two tables with different number of Columns in Query.