Summary

The Excel CHOOSECOLS function returns specific columns from an array or range. The columns to return are provided as numbers in separate arguments. Each number corresponds to the numeric index of a column in the given array.

Purpose 

Return specific columns from an array

Return value 

Extracted columns in a single array

Syntax

=CHOOSECOLS(array,col_num1,[col_num2],...)
  • array - The array to extract columns from.
  • col_num1 - The numeric index of the first column to return.
  • col_num2 - [optional] The numeric index of the second column to return.

How to use 

The Excel CHOOSECOLS function returns specific columns from an array or range. The columns to return are provided as numbers in separate arguments. Each number corresponds to the numeric index of a column in the source array. The result from CHOOSECOLS is always a single array that spills onto the worksheet.

The first argument in the CHOOSECOLS function is array. Array can be a range, or an array from another formula. Additional arguments are in the form col_num1col_num2col_num3, etc. Each number represents a specific column to extract from the array, and should be supplied as a whole number.

Basic usage

To get columns 1 and 3 from an array, you can use CHOOSECOLS like this:

=CHOOSECOLS(A1:C5,1,3) // columns 1 and 3

To get the same two columns in reverse order:

=CHOOSECOLS(A1:C5,3,1) // columns 3 and 1

CHOOSECOLS will return a #VALUE! error if a requested column number is out of range:

=CHOOSECOLS(A1:C5,4) // returns #VALUE!

With array constants

Another option for specifying which columns to return is to use an array constant like {1,2,3} as the second argument (col_num1). In the example below, the formula in H3 is:

=CHOOSECOLS(B3:F9,{1,3,5})

With the array constant {1,3,5} given as the second argument, CHOOSECOLS returns columns 1, 3, and 5:

CHOOSECOLS with array constant

The array constant provided can be in the form {1,2,3} or {1;2;3}.

With negative column numbers

A nice feature of CHOOSECOLS is that you can use negative column numbers to extract columns from the end of a range. For example, to get the last column of a range, you can use a formula like this:

=CHOOSECOLS(range,-1)

To get the second to last column, you can use:

=CHOOSECOLS(range,-2)

To get the last three columns in the order that they appear:

=CHOOSECOLS(range,-3,-2,-1)

You can also mix negative and positive row numbers. To return the first and last columns at the same time:

=CHOOSECOLS(range,1,-1)

With arrays

As seen above, you can use an array constant as the second argument in CHOOSECOLS to indicate columns. You can also use an array created with a formula. For example, the formula below uses CHOOSECOLS and the SEQUENCE function to reverse the order of columns in an array:

=CHOOSECOLS(array,SEQUENCE(COLUMNS(array),,COLUMNS(array),-1))

When given a 3-column range or array, SEQUENCE returns {3;2;1} to CHOOSECOLS, and CHOOSECOLS returns the 3 columns in reverse order:

CHOOSECOLS function - reverse column order

The formula returns all the columns in Array, starting with the last column.

Notes

  • CHOOSECOLS will return a #VALUE error if a column number is out of range.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.