Excel CHOOSEROWS function

Summary

The Excel CHOOSEROWS function is designed to return specific rows from an array or range. Similar to CHOOSECOLS, this function allows you to specify the rows you want to extract by providing their numeric indices as separate arguments. CHOOSEROWS is useful for selectively retrieving rows from a larger dataset, reorganizing row order, or simplifying complex arrays for analysis or reporting.
Syntax
				
					=CHOOSEROWS(array, row_num1, [row_num2], ...)
				
			
  • array: The array or range to extract rows from
  • row_num1: The numeric index of the first row to return
  • row_num2: [Optional] The numeric index of the second row to return, and so on
Return value
Extracted rows are combined into a single array.

How to use

To use CHOOSEROWS, first specify the array or range you want to extract rows from. Then, add the row indices as additional arguments. Each argument should be a whole number representing the row’s position in the source array.

Examples

Simple CHOOSEROWS
Extracting Specific Rows from a Range: To select the 1st and 3rd rows from a given range:
				
					=CHOOSEROWS(A1:C5, 1, 3)
				
			
This formula selects the 3rd and then the 1st row from the range A1:C5, effectively reversing their order in the output array.
CHOOSEROWS with Reversed Rows
Reversing Row Order: To retrieve rows in reverse order from a range:
				
					=CHOOSEROWS(A1:C5, 3, 1)
				
			
This formula selects the 3rd and then the 1st row from the range A1:C5, effectively reversing their order in the output array.
CHOOSEROWS with Out-of-Range Rows
Error Handling for Out-of-Range Rows: To demonstrate error handling when a requested row number is out of range:
				
					=CHOOSEROWS(A1:C5, 6)
				
			
Since the range A1:C5 only has five rows, requesting the 6th row results in a #VALUE! error.
CHOOSEROWS with Array Constants
Using Array Constants to Specify Rows: To select multiple rows using an array constant:
				
					=CHOOSEROWS(B3:F9, {1, 4, 7})
				
			
With the array constant {1, 4, 7}, this formula returns the 1st, 4th, and 7th rows from the range B3:F9.
CHOOSEROWS with Dynamic Arrays
Reversing Rows Order Using Dynamic Arrays: To reverse the row order of a range dynamically:
				
					=CHOOSEROWS(A1:C5, SEQUENCE(ROWS(A1:C5),,ROWS(A1:C5),-1))
				
			
In this example, SEQUENCE generates a reversed array of row indices for CHOOSEROWS, returning the rows of A1:C5 in reverse order.

Additional Notes

  • CHOOSEROWS is a versatile function that can greatly aid in data analysis and report preparation by allowing selective row extraction.
  • It simplifies data manipulation tasks, especially when working with large datasets or reorganizing data for specific analyses.
  • Remember that CHOOSEROWS will return a #VALUE! error if any of the specified row numbers are outside the range of the source array.

Related Functions

Excel EXPAND function

The Excel EXPAND function enlarges arrays by adding rows/columns, ideal for dataset dimension increase or padding with values.

Excel DROP function

The Excel DROP function trims arrays by removing specified rows and columns, perfect for focusing analysis on relevant data sections.

Excel TAKE function

The Excel TAKE function extracts a specific subset of rows and columns from an array, ideal for isolating sections of a dataset.

Excel CHOOSECOLS function

The Excel CHOOSECOLS function extracts specific columns from a range, ideal for reorganizing or simplifying datasets for analysis.

Content Navigation