|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

CHOOSEROWS

CHOOSEROWS

  • Last updated on

Description

Use this function to extract specific rows from a range or array.

Syntax

CHOOSEROWS(array, row_1, …,[row_1048576])

Inputs

This function accepts the following argument:

Name Required Description Valid Input
array Yes The range or array from which to select rows. A cell, a cell range, or a formula which results in either of these.
row_1, …, [row_1048576] No Row numbers to extract from the array. Up to 1,048,576 rows can be identified. A positive integer, a reference to a cell that contains a positive integer, 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
4 Bob 42 San Francisco 85000
5 Robin 25 Denver 55000

Sample formulas

Use case Formula Result and Explanation
Create a new data set that has the same header and the contents of the third row. =CHOOSEROWS(A1:D4, 1, 3)

Using the data table above, if the formula is in cell E1, this produces the following:

E F G H
Name Age City Salary
Alice 28 Chicago 65000
Create a new data set that has the same header and the contents of rows 2 and 4. =CHOOSEROWS(A1:D4, 4, 2, 1)

Using the data table above, if the formula is in cell E1, this produces the following:

E F G H
Name Age City Salary
Bob 42 San Francisco 85000
John 35 New York 75000
Create a new data set with only the contents of row 2. =CHOOSEROWS(A1:D4, 2)

Because the formula only asks for row 2, the header row is not included. Using the data table above, if the formula is in cell E1, this produces the following:

E F G H
John 35 New York 75000

Create a new data set listing the 3 oldest employees dynamically without listing them manually. Dynamically in this case means that the number of rows returned can be determined either by a fixed or referenced value.

Tip: In this case the value is fixed as 3, but equally, the value for SEQUENCE could be a cell reference, in which case, the number of rows returned would be determined by the value in that cell.

=CHOOSEROWS(FILTER(A2:D5, B2:B5>25), SEQUENCE(3))

This formula first uses FILTER(A2:D5, B2:B5>25) to define what is to be considered. In this case where Age (column B) is greater than 30. The value for SEQUENCE(3) then specifies how many rows are to be used.

Using the data table above, if the formula is in cell E1, this produces the following:

E F G H
John 35 New York 75000
Alice 28 Chicago 65000
Bob 42 San Francisco 85000

 

Notes

  • Row numbers start from 1, not 0.
  • As with all array functions, FILTER dynamically populates results across adjacent cells, starting from the cell containing the FILTER function.
  • CHOOSEROWS:
    • Can select and reorder rows dynamically.
    • Works with both static ranges and dynamic arrays.
    • Is helpful for creating subsets of large datasets.

Tips

  • If you have header row(s), start your array values one row down (for example, in the above data set start with A2 rather than A1) if you don't want the headers to be included.
  • CHOOSEROWS can be combined with other functions, including:
    • FILTER: To select specific rows from filtered data.
    • SORT: To rearrange rows before or after sorting.
    • UNIQUE: To extract unique values from selected rows.

Here are the other array formula functions supported in Workiva spreadsheets.

Was this article helpful?
0 out of 0 found this helpful
DescriptionUse this function to extract specific rows from a range or array.SyntaxCHOOSEROWS(array, row_1, …,[row_104...