Function Reference: CHOOSECOLS, CHOOSEROWS
| Syntax | ||
| =CHOOSECOLS(array,col_num1,[col_num2],...[col_num253]) =CHOOSEROWS(array,row_num1,[row_num2],...[row_num253]) |
||
| Purpose / Description | ||
| Returns an array of values extracted from the given columns (CHOOSECOLS) or rows (CHOOSEROWS) of the array referenced.. | ||
| Arguments | ||
| Name | Type | Description |
| array | reference or array of any | A reference or nested expression that returns a valid reference, a constant array of any values or a nested expression that returns an array of any values. |
| col_num1 row_num1 |
number | For CHOOSECOLS: A reference to a range, a nested expression or a constant value that is a number or array of numbers each representing the column number of the first column to be returned from array. If the value is positive, the column returned is determined by counting from the leftmost column of array, if the value is negative, the column returned is determined by counting from the rightmost column of array. For CHOOSEROWS: A reference to a range, a nested expression or a constant value that is a number or array of numbers each representing the row number of the first row to be returned from array. If the value is positive, the row returned is determined by counting from the topmost row of array, if the value is negative, the row returned is determined by counting from the bottom most row of array. |
| [col_num2]... [Col_num253] [row_num2]... [row_num253] |
number | For CHOOSECOLS: Optional references to ranges, nested expressions or constant values that are numbers being the column number of the extra column(s) to be returned from array. If the values are positive, the columns returned are determined by counting from the leftmost column of array, if the values are negative, the columns returned are determined by counting from the rightmost column of array. For CHOOSEROWS: Optional references to ranges, nested expressions or constant values that are numbers being the row number of the extra row(s) to be returned from array. If the values are positive, the rows returned are determined by counting from the topmost row of array, if the values are negative, the rows returned are determined by counting from the bottom most row of array. |
| Return Values | ||
| Type | Specific Value | Description |
| array of any | Regardless of whether the original array is a reference to a range, a calculated array, or a constant array, the result returned is an array containing the values from the selected column(s) or row(s). | |
| error | #VALUE! | One of the values passed to the col_numx or row_numx arguments is either 0 or has a value greater than the number of columns or rows in the array. |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Comment: | These are extremely useful additions to Excel, allowing for the extraction of blocks of data from within ranges or arrays. | |
| Comment: | The sign of the col_numx or row_numx arguments is useful in allowing the direction from the edge of the array or range to be switched, allowing for some simpler references. | |
| Comment: | Each of the col_numx or row_numx arguments accepts an array expression or array of constants, simplifying the way in which sets of column numbers can be passed, for instance using a SEQUENCE function. | |
| Examples: | Open / download sample workbook: CHOOSECOLS, CHOOSEROWS.xlsx | |