CHOOSEROWS

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

Returns the array with just a specifiic number of rows.

arrayThe array(s) to append.
row_num1The row number to be returned.
row_num2(Optional) The row number to be returned.

REMARKS
* This function was added in Microsoft 365.
* This function can create a Dynamic Array Formula.
* If "row_num" > 0 then rows are counted from the top.
* If "row_num" < 0 then rows are counted from the bottom.
* If "row_num" > the number of rows in the array, then #VALUE! is returned.
* If "row_num" = 0, then #VALUE! is returned.
* You can use the CHOOSECOLS function to return the array with just a specific number of columns.
* You can use the DROP function to return the array without certain rows or columns.
* You can use the EXPAND function to return the array expanded or padded to specific dimensions.
* You can use the TAKE function to return the intersection of specific rows and columns in an array.
* This function was first released in March 2022.
* For the Microsoft documentation refer to support.microsoft.com

 ABCDE
1=CHOOSEROWS(C1:E3,1) = {1,2,3} 123
2=CHOOSEROWS(C1:E3,-1) = {7,8,9} 456
3=CHOOSEROWS(C1:E3,2) = {4,5,6} 789
4=CHOOSEROWS(C1:E3,-2) = {4,5,6}    
5=CHOOSEROWS(C1:E3,3) = {7,8,9}    
6=CHOOSEROWS(C1:E3,1,3) = {1,2,3 ; 7,8,9}    
7=CHOOSEROWS(C1:E3,4) = #VALUE!    
8=CHOOSEROWS(C1:E3,1,0) = #VALUE!    

1 - What is the first row from the cell range "C1:E3".
2 - What is the last row from the cell range "C1:E3". A negative number is used to count from the bottom.
3 - What is the second row from the cell range "C1:E3".
4 - What is the second to last row from the cell range "C1:E3". A negative number is used to count from the bottom.
5 - What is the third row from the cell range "C1:E3".
6 - What are the first and third rows from the cell range "C1:E3".
7 - What is the fourth row from the cell range "C1:E3". There are not four rows.
8 - What happens when one of the row numbers is zero.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top