How to use the CHOOSEROWS function

Author: Oscar Cronquist Article last updated on May 05, 2022

CHOOSEROWS function example

The CHOOSEROWS function returns given rows from a cell range or array.

Dynamic array formula in cell B9:

=CHOOSEROWS(B2:D5, 2, 4)

The CHOOSEROWS function is available to Excel 365 users and is in the "Array manipulation" category.

CHOOSEROWS Function Syntax
CHOOSEROWS Function Arguments
CHOOSEROWS Function example
CHOOSEROWS Function example - array
CHOOSEROWS Function example - split text to array
CHOOSEROWS Function example - negative values
CHOOSEROWS Function errors
CHOOSEROWS Function - values in random order
CHOOSEROWS Function - multiple source ranges
Get every other row

1. CHOOSEROWS Function Syntax

CHOOSEROWS(array, row_num1, [row_num2], …)

2. CHOOSEROWS Function Arguments

array	Required. The source cell range or array.
row_num1	Required. A number representing the row in a given cell range.
[row_num2]	Optional. Extra row numbers to be extracted.

3. CHOOSEROWS Function example

CHOOSEROWS function example1

The picture above shows how the CHOOSEROWS function extracts the second and fourth row from cell range B2:D5.

Dynamic array formula in cell B9:

=CHOOSEROWS(B2:D5, 2, 4)

Note! If any of the values in the source range change, the values in the CHOOSEROWS function output change as well.

3.1 Explaining formula

Step 1 - CHOOSEROWS function

CHOOSEROWS(array, row_num1, [row_num2], …)

Step 2 - Populate arguments

array - B2:D5
row_num1 - 2
[row_num2] - 4

Step 3 - Evaluate function

CHOOSEROWS(B2:D5, 2, 4)

becomes

CHOOSEROWS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, 2)

and returns

{68, 84, 98;
37, 63, 45}

4. CHOOSEROWS Function example - array

CHOOSEROWS function array

The CHOOSEROWS function lets you use an array in the arguments, the image above shows an array in the first argument.

Dynamic array formula in cell B9:

=CHOOSEROWS(B2:D5, {1; 4})

Explaining formula

Step 1 - Populate the array

The curly brackets let you build an array that you can use in most but not all Excel functions. The ; semicolon is a delimiting character that separates values row by row.

The CHOOSE function requires numbers separated with semicolons or whatever row delimiting character you use.

{1; 4}

Step 2 - CHOOSEROWS function

CHOOSEROWS(array, row_num1, [row_num2], …)

CHOOSEROWS(B2:D5, {1; 4})

becomes

CHOOSEROWS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, {1; 4})

and returns

{89, 27, 26;
37, 63, 45}

5. CHOOSEROWS Function example - split text to array

CHOOSEROWS function TEXTSPLIT function2

The section shows how to return specific rows based on a string containing numbers. The TEXTSPLIT function separates

Dynamic array formula in cell B9:

=CHOOSEROWS(B2:D5,TEXTSPLIT(F2,,",")*1)

Explaining formula

Step 1 - Split text

The TEXTSPLIT function lets you split a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(F2,,",")

becomes

TEXTSPLIT("1,3,4",,",")

and returns {"1"; "3"; "4"}. Note the double quotes, Excel handles these values as text values. The next step converts text values to numbers.

Step 2 - Convert text to numbers

The asterisk lets you multiply numbers in an Excel formula, I am using it here to convert text to numbers.

TEXTSPLIT(F2,,",")*1

becomes

{"1"; "3"; "4"}*1

and returns {1; 3; 4}.

Step 3 - CHOOSEROWS function

CHOOSEROWS(B2:D5,TEXTSPLIT(F2,,",")*1)

becomes

CHOOSEROWS(B2:D5, {1; 3; 4})

becomes

CHOOSEROWS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, {1; 3; 4})

and returns

{89,27,26;
19,92,62;
37,63,45}

6. CHOOSEROWS Function example - negative values

CHOOSEROWS function negative values1

The CHOOSEROWS function lets you use negative numbers, this will make the function count from the bottom or from the end.

Formula in cell B9:

=CHOOSEROWS(B2:D5, -4)

CHOOSEROWS(array, row_num1, [row_num2], …)

7. CHOOSEROWS function error

CHOOSEROWS function error value

The CHOOSEROWS function returns a #VALUE! error when no values can be displayed.

8. CHOOSEROWS function - values in random order

CHOOSEROWS function values in random order 1

The image above shows a formula that returns values in random order from the two first rows in a 2D cell range.

Dynamic array formula in cell B8:

=LET(z, CHOOSEROWS(B2:D5, 2), x, TOROW(z), WRAPCOLS(SORTBY(x, RANDARRAY(, COLUMNS(x))), ROWS(z)))

8.1 Explaining formula in cell B8

Step 1 - Remove two first rows

CHOOSEROWS(B2:D5, 2, 4)

becomes

CHOOSEROWS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, 2, 4)

and returns

{68,84,98;
37,63,45}.

Step 2 - Rearrange values to a single row

The TOROW function rearranges values from a 2D cell range or array to a single row.

TOROW(array, [ignore], [scan_by_col])

TOROW(CHOOSEROWS(B2:D5,2,4))

becomes

TOROW({68,84,98;
37,63,45})

and returns

{68, 84, 98; 37, 63, 45}.

Step 3 - Count cells

The COLUMNS function returns the number of columns in a given cell range or array.

COLUMNS(array)

COLUMNS(TOROW(CHOOSEROWS(B2:D5,2, 4)))

becomes

COLUMNS({68, 84, 98, 37, 63, 45})

and returns 6.

Step 4 - Count rows

The ROWS function returns the number of rows in a given cell range or array.

ROWS (array)

ROWS(CHOOSEROWS(B2:D5,2, 4))

becomes

ROWS({68, 84, 98; 37, 63, 45})

and returns 2.

Step 5 - Create random decimal numbers

The RANDARRAY function returns a table of random numbers across rows and columns.

RANDARRAY([rows], [columns], [min], [max], [whole_number])

RANDARRAY(,COLUMNS(TOROW(CHOOSEROWS(B2:D5,2, 4))))

becomes

RANDARRAY(,6)

and returns

{0.215398134613085, 0.390607168196479, ... ,0.83231474462401}.

Step 6 - Rearrange values in random order

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(TOROW(CHOOSEROWS(B2:D5,2, 4)),RANDARRAY(,COLUMNS(CHOOSEROWS(B2:D5,2, 4))))

becomes

SORTBY({68, 84, 98, 37, 63, 45}, {0.215398134613085, 0.390607168196479, ... ,0.83231474462401})

and returns

{63, 37, 45, 98, 84, 68}

Step 7 - Rearrange values to the original array size

The WRAPCOLS function rearranges values from a single row to a 2D cell range based on a given number of values per column.

WRAPCOLS(vector, wrap_count, [pad_with])

WRAPCOLS(SORTBY(TOROW(CHOOSEROWS(B2:D5,2, 4)),RANDARRAY(,COLUMNS(TOROW(CHOOSEROWS(B2:D5,2, 4))))),ROWS(CHOOSEROWS(B2:D5,2, 4)))

becomes

WRAPCOLS({63, 37, 45, 98, 84, 68}, ROWS(CHOOSEROWS(B2:D5,2, 4)))

becomes

WRAPCOLS({63, 37, 45, 98, 84, 68}, 2)

and returns

{63, 37, 45; 98, 84, 68}.

Step 8 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

WRAPCOLS(SORTBY(TOROW(CHOOSEROWS(B2:D5, 2, 4)), RANDARRAY(, COLUMNS(TOROW(CHOOSEROWS(B2:D5, 2, 4))))), TOROW(CHOOSEROWS(B2:D5, 2, 4)))

becomes

LET(z, CHOOSEROWS(B2:D5, 2, 4), x, TOROW(z), WRAPCOLS(SORTBY(x, RANDARRAY(, COLUMNS(x))), ROWS(z)))

9. CHOOSEROWS Function - multiple source ranges

CHOOSEROWS function from multiple sources

The picture above shows a formula that merges three non-contiguous cell ranges and shows rows 1, 5, and 9.

Dynamic array formula in cell B9:

=CHOOSEROWS(VSTACK(B3:D6, F3:H6, J3:L6), 1, 5, 9)

9.1 Explaining formula

Step 1 - Stack values horizontally

The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

VSTACK(array1, [array2],...)

VSTACK(B3:D6, F3:H6, J3:L6)

becomes

VSTACK({"Peach", 43, 1.03;"Blueberry", 39, 1.48;"Apple", 46, 1.1;"Grapefruit", 14, 0.72}, {"Mandarin", 29, 0.78;"Raspberry", 33, 1.07;"Plum", 25, 0.9;"Mango", 37, 1.13}, {"Pear", 17, 0.63;"Orange", 31, 1.06;"Lime", 17, 1.27;"Kiwi", 45, 0.58})

and returns

{"Peach", 43, 1.03;
"Blueberry", 39, 1.48;
"Apple", 46, 1.1;
"Grapefruit", 14, 0.72;
"Mandarin", 29, 0.78;
"Raspberry", 33, 1.07;
"Plum", 25, 0.9;
"Mango", 37, 1.13;
"Pear", 17, 0.63;
"Orange", 31, 1.06;
"Lime", 17, 1.27;
"Kiwi", 45, 0.58}

Step 2 - Filter given rows

CHOOSEROWS(VSTACK(B3:D6, F3:H6, J3:L6),1, 5, 9)

becomes

CHOOSEROWS({"Peach", 43, 1.03;
"Blueberry", 39, 1.48;
"Apple", 46, 1.1;
"Grapefruit", 14, 0.72;
"Mandarin", 29, 0.78;
"Raspberry", 33, 1.07;
"Plum", 25, 0.9;
"Mango", 37, 1.13;
"Pear", 17, 0.63;
"Orange", 31, 1.06;
"Lime", 17, 1.27;
"Kiwi", 45, 0.58}, 1, 5, 9)

and returns

{"Peach", 43, 1.03;
"Mandarin", 29, 0.78;
"Pear", 17, 0.63}

10. CHOOSEROWS Function - get every other row

CHOOSEROWS function every other row

The formula in cell F4 extracts every other row in cell range B3:D14, the dynamic array formula calculates how many rows are needed automatically based on the cell reference.

Formula in cell F4:

=CHOOSEROWS(B3:D14, SEQUENCE(ROUNDUP(ROWS(B3:D14)/2,0), , 1, 2))

10.1 Explaining formula

Step 1 - Count rows

The ROWS function returns a number corresponding to the number of rows in a given cell reference.

ROWS(array)

ROWS(B3:D14)

returns 12. There are twelve rows in cell reference B3:D14.

Step 2 - Round number up

The ROUNDUP function rounds a number up.

ROUNDUP(number, num_digits)

ROUNDUP(ROWS(B3:D14)/2,0)

becomes

ROUNDUP(12/2,0)

becomes

ROUNDUP(6,0)

and returns 6.

Step 3 - Create a sequence of numbers from 1 to n step 2

The SEQUENCE function creates a list of sequential numbers.

SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(ROUNDUP(ROWS(B3:D14)/2,0), , 1, 2)

becomes

SEQUENCE(6, , 1, 2)

and returns {1; 3; 5; 7; 9; 11}.

Step 4 - Get rows

CHOOSEROWS(B3:D14, SEQUENCE(ROUNDUP(ROWS(B3:D14)/2,0), , 1, 2))

becomes

CHOOSEROWS(B3:D14, {1; 3; 5; 7; 9; 11})

and returns

{"Peach", 43, 1.03;
"Apple", 46, 1.1;
"Mandarin", 29, 0.78;
"Plum", 25, 0.9;
"Pear", 17, 0.63;
"Lime", 17, 1.27}

Functions in this article

CHOOSEROWS TEXTSPLIT LET TOROW WRAPCOLS SORTBY RANDARRAY COLUMNS ROWS VSTACK SEQUENCE ROUNDUP

Functions in 'Array manipulation' category

The CHOOSEROWS function function is one of many functions in the 'Array manipulation' category.

CHOOSECOLS function

Returns given columns from a cell range or array.

CHOOSEROWS function

Returns given rows from a cell range or array.

DROP function

Removes a given number of rows or columns from a 2D cell range or array.

EXPAND function

Increases a cell range or array by a specified number of columns and rows.

HSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

TAKE function

Returns a given number of rows or columns from a 2D cell range or array.

TOCOL function

Rearranges values in 2D cell ranges to a single column.

TOROW function

Rearranges values from a 2D cell range to a single row.

VSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

WRAPCOLS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

WRAPROWS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

How to use the CHOOSEROWS function

Table of Contents

1. CHOOSEROWS Function Syntax

2. CHOOSEROWS Function Arguments

3. CHOOSEROWS Function example

3.1 Explaining formula

Step 1 - CHOOSEROWS function

Step 2 - Populate arguments

Step 3 - Evaluate function

4. CHOOSEROWS Function example - array

Explaining formula

Step 1 - Populate the array

Step 2 - CHOOSEROWS function

5. CHOOSEROWS Function example - split text to array

Explaining formula

Step 1 - Split text

Step 2 - Convert text to numbers

Step 3 - CHOOSEROWS function

6. CHOOSEROWS Function example - negative values

7. CHOOSEROWS function error

8. CHOOSEROWS function - values in random order

8.1 Explaining formula in cell B8

Step 1 - Remove two first rows

Step 2 - Rearrange values to a single row

Step 3 - Count cells

Step 4 - Count rows

Step 5 - Create random decimal numbers

Step 6 - Rearrange values in random order

Step 7 - Rearrange values to the original array size

Step 8 - Shorten formula

9. CHOOSEROWS Function - multiple source ranges

9.1 Explaining formula

Step 1 - Stack values horizontally

Step 2 - Filter given rows

10. CHOOSEROWS Function - get every other row

10.1 Explaining formula

Step 1 - Count rows

Step 2 - Round number up

Step 3 - Create a sequence of numbers from 1 to n step 2

Step 4 - Get rows

Functions in this article

Functions in 'Array manipulation' category

Excel function categories

Excel categories

Leave a Reply

How to comment