To dynamically get the last column from a data range in Google Sheets, I can offer two formulas for you.

The first formula is based on Query and the second one is based on Indirect. But in both of these formulas, to get the last column number, we will use one key formula.

What’s that key formula?

In one of my earlier tutorials, i.e. Find the Last Non-Empty Column in a Row in Google Sheets, I have provided one formula.

We will use that formula in Query as well as in Indirect to dynamically get the last column from a data range in Google Sheets.

Purpose of Extracting the Last Column from a Data Range in Google Sheets

If your data grows horizontally, you may want to get the last column from that data for the below reasons (there may be some other reason too).

1. To use the first column (probably a description column) and the last column (number column) to dynamically create a chart.
2. For quickly viewing the data from the last non-empty column from an evergrowing dataset.
3. To get the last column from a data range to use in array formulas as an expression.

The above scenario normally happens when you have daily, weekly, fortnightly, monthly, quarterly, half-yearly, or yearly data in columns but not limited to.

For example, the first column contains descriptions of items, the second column contains some data for January, the third column contains some data for February, and so on.

Example 1:

Example 2:

From the above two examples, we can understand one key feature of the formula that you are going to get.

What’s that?

We can understand that the formula uses an infinite horizontal range (open column range) and correctly gets the last non-empty column from that range.

Key Formula

For our formula examples, we can consider the range B2:6 (infinite columns starting from column B and 6 rows).

You feel free to include as many rows as you want and even change the starting column from B2 to any other column and row. But right now, please stick with the above range.

=ArrayFormula(
     IFNA(
        match(2,1/(B2:2<>""))
     )
)

This formula will return 5 if the data is as per example 1 and 7 if the data is as per example 2.

For the formula explanation, please check my related tutorial, which I have already linked above.

The above is the key formula that helps us to dynamically get the last non-empty column from a range in Google Sheets. We are going to use it in Query and then in Indirect below.

Query Formula to Get the Last Column from a Range in Google Sheets

If you check the Query clause order, you can see the use of the “SELECT” clause. In the “SELECT” clause, we can use column numbers to get the corresponding column.

Normally we use column letters to refer to columns in the Query function.

=query(
     B2:6,
     "Select B"
)

If we use column numbers, the syntax will look like as below.

=query(
     {B2:6},
     "Select Col1"
)

In the second formula, we can use our key formula to dynamically get the last column from a range in Google Sheets. Here is the Query formula.

=ArrayFormula(
     query(
        {B2:6},
        "Select Col"&IFNA(match(2,1/(B2:2<>"")))
     )
)

Indirect Formula to Get the Last Column from a Range in Google Sheets

For our said purpose, actually the Query is easy to code (write) and simple to read. But we can use Indirect too. Here are those steps.

Here in the first step, we will convert the last column number that returned by the key formula to the last column letter. To do that we can use the below generic formula.

Generic Formula:

=ArrayFormula(
     address(
        2,
        key_formula
     )
)

Please note one thing!

In the key formula, even if our range is starting from column B, we must use the very first column in that sheet, I mean we must use A2:2, not B2:2.

Formula:

=ArrayFormula(
     address(
        2,
        IFNA(match(2,1/(A2:2<>"")))
     )
)

Formula Explanation: How to Convert Column Number to Letter in Google Sheets.

The above formula would return the first cell address of the last column. To get the last cell address of the last column, use it as below.

=ArrayFormula(
     address(
        6,
        IFNA(match(2,1/(A2:2<>"")))
     )
)

Combine both and insert a : in-between. Then wrap it with Indirect and voila!

=ArrayFormula(
     indirect(
        address(2,IFNA(match(2,1/(A2:2<>""))))
        &":"&
        address(6,IFNA(match(2,1/(A2:2<>""))))
     )
)

Combine First Column and the Dynamic Last Column

Scroll to the top and see the point # 1 under the title “Purpose…”.

We can combine the first column with the dynamic last column in order to create a dynamic chart.

To combine the first column with the last column simply write the formula as per the below generic formula (syntax).

{first_columns,query_formula}

So the formula will be (as per the sample data);

={
     B2:B6,
     ArrayFormula(
        query(
           {B2:6},
           "Select Col"&IFNA(match(2,1/(B2:2<>"")))
        )
     )
}

That’s all. Enjoy!