Dynamic Column Reference in Google Sheets Query [Example Formula]

Dynamic column reference in Google Sheets Query is a reality and I’ve used this in two of my earlier tutorials. That tutorial links you can find at the end of this post. It’s not important to read that posts at this moment. First, understand how to dynamically return columns in Query.

To identify columns you can use two types of column identifiers in the Select clause in Query. They are column letters like A, B, C or column numbers like Col1, Col2, Col3. I am using the latter to dynamically refer columns in Google Sheets Query. Don’t worry! I’ll explain it later.

Understand the Dynamic Column Reference in Google Sheets Query

You can interpret dynamic columns in Query in many ways. For example, I have a dataset with 50 number of columns. I can useSelect * to return all the columns.

If I want specific columns I can useSelect A, D, Z orSelect Col1, Col4, Col26. But what about returning column number 10 to 20 or 5 to 45?

The normal solution is to change the data range in Query as below.

If your data range is A1: AX, you can use the range like J1: AX to return the columns 10 to 50. Next time when you want to return the columns from 1 to 20, you want to manually change the formula. So it’s not dynamic.

I want a dynamic Query formula that can control the columns from outside of the formula. Here is a 13 column table that shows monthly data in columns.

See how I am controlling the columns dynamically.

dynamically control columns in Query Select Clause in Google Sheets In this, I can select columns of specific months like January to December, March to April, or October only. To do that I only need to change the numbers in the cells C9 or F9. These two cells control the column output of Query.

Many Google Sheets users think that it’s not possible in Query as the Query Select Clause takes strings as column Identifiers. It’s partially true but there is a workaround.

How to Get Dynamic Column Identifiers in Select Clause in Query

In my example, I am going to control the Query column numbers from outside the formula. The sample data is the same as per the above example that in the range A1: M5.

First, see two-three basic Query formulas using the select Clause. Then I will come to how to get dynamic Column reference in Google Sheets Query.

Query Formula 1:

This formula returns all the columns in the range.

=query(A1:M5,"Select *")

Query Formula 2:

The below formulas return the Columns 1 to 3. Both of the below formulas return the same outputs.

Using Column Letter as Identifier

=query(A1:M5,"Select A, B, C")

Using Column Numbers as Identifier

=query({A1:M5},"Select Col1,Col2,Col3")

To make a dynamic Column reference in Google Sheets Query, you should follow the just above formula where I’ve used Column Numbers as identifiers. See how to do that.

Steps to Create Dynamic Column Reference in Query

If you check the above screen capture, you can understand that I am controlling the column numbers in Query using the cells C9 and F9.

The cell C9 contains the starting column number and the cell F9 contains the ending column number.

That means If I put the number 5 in cell C9 and 10 in cell F9, the Query formula would return the columns from 5 to 10.

In order to get that I should specify column identifiers as below in the Query Select clause but that should be generated automatically.

Col5, Col6, Col7, Col8, Col9, Col10

Here is that formula to get dynamic column reference in Google Sheets Query.

=ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A"&M9&":A"&P9)))))

You can use this formula in the Select Clause in Query. How?

Formula That Generates Dynamic Column in Google Sheets Query

This is my master formula.

=QUERY({A1:M5},"Select "&ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A"&C9&":A"&F9))))))

This formula is equal to the below formula as per the current values in the control Cells C9 and F9.

=QUERY({A1:M5},"Select Col5, Col6, Col7, Col8, Col9, Col10")

Now in order to control the column numbers just change the numbers in the cell C9 and F9.

How Can I Make Use of this Dynamic Query Formula in My Own Data Range?

You can copy this formula from here and use it. You should change the data range A1: M5 to your data range. Then change the cell reference C9 and F9 that pointing to the cells where you have entered your starting and ending column numbers.

If you use infinite data range like A1: M, then don’t forget to move the control cells C9 and F9 out of this rage.

Sticky First Column in Query with Dynamic Column Control

See my sample data. The first column is the description column. I want to make it sticky. I only want to control the month columns using the control numbers entered in cells.

Then use the formula as below.

={index(A1:A7),QUERY({B1:M7},"Select "&ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A"&C9&":A"&F9))))))}

You can easily identify the changes if you compare it with the earlier master formula. Now the logic part.

How the Formula Generates Dynamic Column References in Query? (Tips for those who want to learn the logic)

It’s simple. Suppose C9 and F9 are our cells to dynamically control the columns in Query.

If the value in C9 is 3 and F9 is 10, you can use the ROW function to return the number 3 to 10 in row-wise.

This is the basic formula.

=ArrayFormula(row(A3:A9))

In this formula the number 3 and 9 to be replaced by the values in the cells C9 and F9. To get that we can use the INDIRECT function as below.

=ArrayFormula(row(indirect("A"&C9&":A"&F9)))

Now slightly modify this formula to return the text “Col” in front of serial numbers. See that modification in the below screenshot.

Get dynamic column references in Query using ROW function

Now we should combine this formula output. Remember! Each of the value should be separated by a Comma. The TEXTJOIN function does this part brilliantly. See my final formula above to understand that.

Must Read:

I have used the above logic in a more advanced way in the below tutorials.

1. How to Use MIN in Array in Google Sheets for Expanded Results

2. How to Find Max Value in Each Row in Google Sheets [Array Formula]

That’s all about Dynamic Column Reference in Google Sheets Query. Enjoy!

23 COMMENTS

Arif I November 28, 2022 At 10:27 pm

Thanks for this. Is there a way to add a WHERE clause that queries the same dynamic column we found in the first part of the query?

As opposed to the “ColX” format, as per the comments above.

- Prashanth November 30, 2022 At 2:16 pm
  
  Hi, Arif I,
  
  Sorry, I couldn’t understand. I suggest you share a sample sheet (URL) with more info via the comment below.
  
An Dang November 4, 2021 At 9:57 pm

Could you please help explain why we need to put the curly brackets {A1:M5} in the query instead of A1:M5?

- Prashanth November 6, 2021 At 11:31 am
  
  Hi, An Dang,
  
  The difference is in the use of column identifiers.
  
  There are two types of column identifiers.
  
  Example:-
  
  Data Range: X1:Z100.
  
  The first column in this range is column X.
  
  You can refer to this column as below (column identifier type 1).
  
  "Select X
  
  It’s the first column in the range X1:Z100.
  
  To refer to that column as (column identifier type 2);
  
  "Select Col1
  
  We must make the range, i.e., X1:Z100, an expression. There comes the use of the Curly Brackets.
  
YR March 17, 2021 At 11:34 am

It works!

Only for me I replaced the original formula and user helper cells so I can re-arrange the order of the columns, and exclude columns I don’t want:

=QUERY({J1:Z14},"Select "&ArrayFormula(textjoin(", ",1,A3:I3)))

where row A1 to I1 has helper cells, with the formula

=iferror("Col"&index(Match("*"&A2&"*", $J$2:$Z$2,0)),"")

A2 etc, refer to keywords that I want to find out of table headers, as the tables are not consistent in labels, but in content.

Peter Gassiraro November 20, 2020 At 12:32 am

Hi Prashanth —

I really appreciate this post it’s very helpful. Can you help me diagnose the formula parse error when I add “where” to my query?

=QUERY({A7:DN130},"Select "&ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A"&41&":A"&118)))) where Col7 = '"&$G167&"'))

- Prashanth November 21, 2020 At 8:15 am
  
  Hi, Peter Gassiraro,
  
  Let me make you understand by taking one of the formulas from my above tutorial.
  
  =QUERY({A1:M5},"Select "&ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A"&C9&":A"&F9))))))
  
  In this the QUERY dynamic column reference formula, if you remove the variables from within the INDIRECT part, it should be something like this.
  
  =QUERY({A1:M5},"Select "&ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A5:A12"))))))
  
  To include the WHERE clause, you can follow the below formula.
  
  =QUERY({A1:M5},"Select "&ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A5:A12")))))&" where Col10=255")
  
  The formula part Col10=255 should be changed based on the criteria type (text/number/date) which you can find in my corresponding tutorial below.
  
  Examples to the Use of Literals in Query in Google Sheets.
  
J August 24, 2020 At 4:19 pm

So I’m trying to use the same principle on a Where clause using the match function, but it won’t work. What am I missing here?

Formula:

=query({A:K},"Select Col1 where "&"Col"&(match($M$5,$A$2:$K$2,0))='N' and "&"Col"&(match($N$5,$A$2:$K$2,0))='Y'")

- Prashanth August 26, 2020 At 9:41 am
  
  Hi, J,
  
  Here is the correct way of using the MATCH function in the QUERY WHERE clause.
  
  =query({A:K},"Select Col1 where Col"&MATCH($M$5,$A$2:$K$2,0)&"='N' and Col"&MATCH($N$5,$A$2:$K$2,0)&"='Y'",1)
  
  Also, I recommend to use the correct range like A2:K instead of A:K in Query.
  
Sheetin'AroundTheBush July 30, 2020 At 4:55 am

Hello,

Any idea how to get a dynamic formula for an importrange sheet selection?

- Prashanth July 30, 2020 At 3:33 pm
  
  Here is the related guide – Dynamic Sheet Names in Importrange in Google Sheets.
  
drappleyea February 26, 2020 At 1:28 am

How would you add a where clause to this?

- Prashanth February 26, 2020 At 8:28 am
  
  Good question!
  
  Use ampersand to join the Query ‘WHERE’ clause as a text string (within double-quotes).
  
  Example:
  
  =QUERY({A1:M5},"Select "&ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A"&C9&":A"&F9)))))&" where Col1='Apple'",1)
  
Austria August 13, 2019 At 11:34 am

Good day,

What will be the formula if it to be liked this,
Instead of Col5 to Col12
I need only Col5 and Col12

- Prashanth August 13, 2019 At 4:02 pm
  
  Hi, Austria,
  
  Normally we can use the Query as =query({A1:E},"Select Col1,Col5") or =query(A1:E,"Select A,E")
  
  To refer these two columns dynamically, use the Query as below.
  
  =query({A1:E},"Select Col"&F1&",Col"&G1)
  
  In this, the cell F1 contains the number 1 and G1 contains the number 5.
  
  Best,
  
  - Austria September 7, 2019 At 9:15 am
    
    Another question,
    
    If I specified Col10 and Col15 instead of listing each column how can I change the formula if I just want to see the sum from Col10 to Col15?
    
    - Prashanth September 7, 2019 At 1:44 pm
      
      Hi, Austria,
      
      Just replace the , delimiter in the Textjoin to a +.
      
      Eg.
      
      The data range is A1:M5 and the first row contains column names.
      
      The value in cell C9 is 2 (indicates the second column and F9 is 5 (indicates the fifth column).
      
      The formula to sum column 2 to 5 is;
      
      In cell O1
      
      =QUERY({A1:M5},"Select "&ArrayFormula(textjoin("+ ",TRUE,("Col"&row(indirect("A"&C9&":A"&F9))))))
      
      The cleaner coding of this formula is;
      
      =query({A1:M5},"Select Col2+Col3+Col4+Col5")
      
      Actually, you can use MMULT for this. See that detail here – Array Formula to Sum Multiple Columns in Google Sheets and Grouping.
      
    - Prashanth September 7, 2019 At 6:50 pm
      
      This too – Sum Multiple Columns Dynamically Across Rows in Google Sheets.
      
Oliver Steadman May 6, 2019 At 7:19 am

Does this dynamic column-selection actually work any more? I just get “NO_COLUMN: Col1” no matter what I try…

- Prashanth May 6, 2019 At 9:18 am
  
  Hi, Oliver Steadman,
  
  It works 🙂
  
  - Ken Lee May 4, 2020 At 4:19 am
    
    I am also unable to use ‘Col#’ instead of A, B, C, etc.
    
    - Prashanth May 4, 2020 At 2:23 pm
      
      Hi, Ken Lee,
      
      The Query ‘data’ should be within open and clause Curly Brackets or an expression.
      
      QUERY(data, query, [headers])
      
      Eg.
      
      =query({A1:F},"Select Col1,Col2")
      
      I’ve explained the same within the tutorial.
      
      - Ken Lee May 4, 2020 At 8:19 pm
        
        Thank you — didn’t see that in the tutorial.