How to use the CHOOSEROWS function

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

Dynamic array formula in cell B9:

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

 

1. CHOOSEROWS Function Syntax

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

Back to top

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.

Back to top

3. CHOOSEROWS Function example

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:

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}

Back to top

4. CHOOSEROWS Function example - 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:

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}

Back to top

5. CHOOSEROWS Function example - split text to array

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

Dynamic array formula in cell B9:

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}

Back to top

6. CHOOSEROWS Function example - negative values

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(array, row_num1, [row_num2], …)

Back to top

7. CHOOSEROWS function error

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

Back to top

8. CHOOSEROWS function - values in random order

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:

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)))

Back to top

9. CHOOSEROWS Function - multiple source ranges

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:

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}

Back to top

10. CHOOSEROWS Function - get 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:

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}

Back to top

Useful links

CHOOSEROWS function - Microsoft support
CHOOSEROWS function in Excel to extract rows from array
Excel CHOOSEROWS Function - my online training hub

Functions in 'Array manipulation' category

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

Excel function categories

Excel categories

Leave a Comment