As its name suggests, the CHOOSECOLS function helps users easily choose columns from a provided array in Google Sheets.
It is one of the new functions Google launched in 2023 for Sheets.
So you may be curious to know the other available options for the said purpose.
Here are the popular alternatives.
Since CHOOSECOLS is a dedicated function for choosing columns, it offers great flexibility.
You will understand the same when you go through the examples below.
Syntax and Arguments
Syntax: CHOOSECOLS(array, col_num1, [col_num2])
Arguments in the CHOOSECOLS Google Sheets Function:
array
: The source array or range.
col_num1
: The column number (in the array) of the first column to be returned.
col_num2,...
: The column number(s), if any, of additional column(s) to be returned.
Column numbers can be positive or negative integers. If it isn’t an integer, the function will round it down.
How to Use the CHOOSECOLS Function in Google Sheets
Here are some basic usage examples of the CHOOSECOLS function in Google Sheets.
Use the following formula to return columns 2 and 4 from the left-hand side of an array.
=choosecols(B2:E7,2,4)
Here is the formula to get columns 2 and 4 from the right-hand side of the array.
=choosecols(B2:E7,-2,-4)
The output will be the columns with the letters “m” and “a” in the top row.
Selecting Multiple Columns
In the above examples, we have used col_num1
and col_num2
arguments to specify columns 2 and 4.
Instead, we can use an array constant like {2,4}
as col_num1
.
It opens a new possibility of using the SEQUENCE within the CHOOSECOLS function in Google Sheets.
Assume we have monthly statuses in columns. There is a blank column between every monthly column.
We can use the SEQUENCE within the CHOOSECOLS to get only the value columns.
=CHOOSECOLS(A1:G7,sequence(4,1,1,2))
It works like this.
The SEQUENCE returns 4 numbers in one column starting from 1.
Since the step value in it is 2, the returned numbers will be 1, 3, 5, and 7. The CHOOSECOLS returns those columns.
Similar: Dynamic Formula to Select Every nth Column in Query in Google Sheets.
CHOOSECOLS Function in Google Sheets for Flipping a Table from Right to Left
There is a purpose for specifying SEQUENCE as an array constant in the above example.
We can adopt that method to flip a table from the right-hand side to the left-hand side in Google Sheets.
Let’s see how to use the CHOOSECOLS function to flip (reverse) a table horizontally in Google Sheets.
Let’s consider the above example. As you can see, the formula returned every other column.
The output table column order is Jan, Feb, Mar, and Apr.
To get the columns in Apr, Mar, Feb, and Jan order, we can use the CHOOSECOLS function in the following way.
=CHOOSECOLS(A1:G7,sequence(4,1,-1,-2))
That’s all. Thanks for the stay. Enjoy.