download the new for mac Ablebits Ultimate Suite for Excel 2024.1.3443.1616

That's how to use the CHOOSEROWS function in Excel to return particular rows from a range or array. To fix it, just clear the obstructing cells. For more information, please see Excel #SPILL! error. Occurs when there are not enough blank cells to fill with the results.

For more details, read How to fix #NAME error in Excel. Currently, CHOOSEROWS is only available in Excel 365 and Excel for the web.

Occurs if the function's name is misspelled or the function is not supported in your Excel. Occurs if the absolute value of any row_num argument is zero or higher than the total number of rows in the array. If the CHOOSEROWS formula results in an error, it's most likely to be one of these reasons. =CHOOSEROWS(A4:D13, TEXTSPLIT(G3, ',') *1)Īs a result, all the specified rows are returned as a single array: The result is a single dynamic array, also known as a spill range.įor example, to get rows 2, 4, 6, 8 and 10 from the range A4:D13, the formula is:Īlternatively, you can use an array constant such as that the CHOOSEROWS function needs, so you embed the TEXTSPLIT formula in the 2 nd argument: You enter the formula in the upper-left cell of the destination range, and it automatically spills into as many columns and rows as needed. Multiple row numbers can be provided individually in separate arguments or in one argument in the form of an array constant.Īs a Excel dynamic array function, CHOOSEROWS handles arrays natively.

A positive number retrieves a corresponding row from the start of the array, a negative number - from the end of the array.

For row_num, provide a positive or negative integer indicating which row to return.

For array, you can supply a range of cells or an array of values driven by another formula.

To pull particular rows from a given array, construct a CHOOSEROWS formula in this way: To get certain columns from a range or array, use the CHOOSECOLS function.

Download the new for mac Ablebits Ultimate Suite for Excel 2024.1.3443.1616