Description
Use this function to extract specific columns from a range or array.
Syntax
CHOOSECOLS(array, col1, …,[col253])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
array |
Yes | The range or array from which to select columns. | A cell, a cell range, or a formula which results in either of these. |
col1, …, [col253] |
Yes | Column numbers to extract from the array. Up to 253 columns can be identified. | A cell reference, a positive integer identifying a column positions (1=A), or a formula which results in either of these. |
Examples
Sample data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Age | City | Salary |
| 2 | John | 35 | New York | 75000 |
| 3 | Alice | 28 | Chicago | 65000 |
Sample formulas
| Use case | Formula | Explanation and Result | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| Extract the Name and City columns from a data set. | =CHOOSECOLS(A1:D3, 1, 3) |
This formula returns columns 1 (Name) and 3 (City), as well as the header. For this data set it would return the following:
|
|||||||||
| Change the order of the columns to Salary, Age, Name. | =CHOOSECOLS(A2:D3, 4, 2, 1) |
This formula reorganizes the columns to the specified order. For this data set it would return the following:
|
|||||||||
| Extract only the "Age" column. | =CHOOSECOLS(A1:D3, 2) |
This formula returns only the "Age" column. For this data set it would return the following:
|
|||||||||
| Find those rows where age is greater than 50 and return the name and the city. | =CHOOSECOLS(FILTER(A1:D10, B1:B10>30), 1, 3) |
This formula first uses FILTER to select rows where column B has values greater than 50, then uses CHOOSECOLS to extract only columns 1 and 3 from those filtered rows. For this data set it would return the following:
Note: If the source data has employee records with Name, Age, City, and Salary, this would return a dynamic array showing Names and City values for employees over 30. The result automatically spills into adjacent cells. |
Notes
- Column numbers start from 1, not 0. So "A" is column 1.
- As with all array functions, CHOOSECOLS dynamically populates results across adjacent cells, starting from the cell containing the CHOOSECOLS function.
- CHOOSECOLS:
- Can select and reorder columns dynamically.
- Works with both static ranges and dynamic arrays.
- Is helpful for creating subsets of large datasets.
Tips
- If you have a header row and don't want the headers to be included, start your array values one row down (for example, in the above data set, start with A2 rather than A1).
- CHOOSECOLS can be combined with other functions, including:
- FILTER: To select specific columns from filtered data.
- SORT: To rearrange columns.
- UNIQUE: To extract unique values from selected columns.
- SEQUENCE: To create dynamic column selections.
- INDEX: For advanced column referencing.
- MATCH: For dynamic column selection based on criteria.
- TRANSPOSE: To reorient selected columns.
- CHOOSEROWS: For more complex data selection.
Related functions
Here are the other array formula functions supported in Workiva spreadsheets.