CHOOSEROWS and CHOOSECOLS functions. Obtain rows and columns at specified positions from range of cells.(Microsoft Excel)
The CHOOSEROWS and CHOOSECOLS functions are new functions added to Excel in September 2022 that retrieve a portion of a cell range.
Similar to, but different from, the TAKE function, the TAKE function takes a number of rows/columns from the beginning/end as arguments, while the CHOOSEROWS and CHOOSECOLS functions take row/column positions as arguments.
How it works
=CHOOSEROWS(array,row_num1 to N) =CHOOSECOLS(array,col_num1 to N)
| Name | Omission | Explanation |
|---|---|---|
| array | Required argument. Specify the target cell range. | |
| row_num1 to N | Required argument. Specifies the position of the rows and columns to be extracted. At least one must be specified. |
Demonstrate
Specify the target cell range as the first argument and the position of the row or column to be retrieved as the second and subsequent arguments.
Specify one or more positions.
Multiple positions can be specified. Another difference from the TAKE function is the ability to extract multiple distant portions at once.
If a negative number is specified, the position is specified from the end.
Filtering columns in CHOOSECOLS can be more efficient than cell specification or copy/paste.
This function is often combined with the Spill function, which returns a range of cells as a result.
It is especially useful when the XLOOKUP function results in a specific column only.
It is also valid for the results of the SORT, SORTBY, and FILTER functions.
---