CHOOSECOLS

Excel Functions

Return specific columns from an array

Syntax

EXCEL

=CHOOSECOLS(array, col_num1, [col_num2], ...)

Arguments

Parameter	Description
`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.

Return Value

Extracted columns in a single array

Details

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 the array, which can be a range, an array constant, or an array generated by another formula. Additional arguments are in the form: col_num1, col_num2, col_num3, etc., and should be the numeric index of the column to extract. To get columns 1 and 3 from an array, you can use CHOOSECOLS like this: To get the same two columns in reverse order: CHOOSECOLS will return a #VALUE! error if a requested column number is out of range:

Examples

Basic usage

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

EXCEL

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

Basic usage

To get the same two columns in reverse order:

EXCEL

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

Basic usage

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

EXCEL

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

With an array constant

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

EXCEL

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

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 colu

EXCEL

=CHOOSECOLS(range,-1)

With negative column numbers

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

EXCEL

=CHOOSECOLS(range,-2)

CHOOSECOLS

Syntax

Arguments

Return Value

Details

Examples

See Also