Excel CHOOSEROWS function to extract certain rows from array (2023)

In this tutorial, we'll take an in-depth look at the Excel 365 function named CHOOSEROWS and its practical uses.

Suppose you have an Excel worksheet with hundreds of rows from which you want to extract some specific ones, say, all odd or even rows, the first 5 or the last 10 rows, etc. Do you already feel annoyed at the thought of copying and pasting the data manually or writing a VBA code to automate the task? Don't worry! All is much simpler than it seems. Just use the new dynamic array CHOOSEROWS function.

Excel CHOOSEROWS function

The CHOOSEROWS function in Excel is used to extract the specified rows from an array or range.

The syntax is as follows:

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


Array (required) - the source array.

Row_num1 (required) - an integer representing the numeric index of the first row to return.

Row_num2, … (optional) - index numbers of additional rows to return.

Here's how the CHOOSEROWS function works in Excel 365:

Excel CHOOSEROWS function to extract certain rows from array (1)

CHOOSEROWS function availability

The CHOOSEROWS function is only available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.

Tip. To get certain columns from a range or array, use the CHOOSECOLS function.

How to use CHOOSEROWS function in Excel

To pull particular rows from a given array, construct a CHOOSEROWS formula in this way:

  1. For array, you can supply a range of cells or an array of values driven by another formula.
  2. For row_num, provide a positive or negative integer indicating which row to return. A positive number retrieves a corresponding row from the start of the array, a negative number - from the end of the array. Multiple row numbers can be provided individually in separate arguments or in one argument in the form of an array constant.

As a Excel dynamic array function, CHOOSEROWS handles arrays natively. 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. The result is a single dynamic array, also known as a spill range.

(Video) Extract all rows from a range that meet criteria in one column [Array Formula]

For example, to get rows 2, 4, 6, 8 and 10 from the range A4:D13, the formula is:

=CHOOSEROWS(A4:D13, 2, 4, 6, 8, 10)

Alternatively, you can use an array constant such as {2,4,6,8,10} or {2;4;6;8;10} to specify the desired rows:

=CHOOSEROWS(A4:D13, {2,4,6,8,10})


=CHOOSEROWS(A4:D13, {2;4;6;8;10})Excel CHOOSEROWS function to extract certain rows from array (2)

Another way to supply the row numbers is entering them in separate cells, and then using either the individual cell references for several row_num arguments or a range reference for a single row_num argument.

For example:

=CHOOSEROWS(A4:D13, F4, G4, H4)


An advantage of this approach is that it lets you extract any other rows by simply changing the numbers in the predefined cells without editing the formula itself.Excel CHOOSEROWS function to extract certain rows from array (3)

Below we will discuss a few more CHOOSEROWS formula examples to handle more specific use cases.

Return rows from the end of an array

To quickly get the last N rows from a range, provide negative numbers for the row_num arguments. This will force the function to count rows from the end of the array.

For instance, to get the last 3 row from the range A4:D13, use this formula:

=CHOOSEROWS(A4:D13, -3, -2, -1)

(Video) Excel Dynamic Array Functions - Video#12: Select Rows & Columns from a table (DROP-TAKE-CHOOSECOLS)

The result will be a 3-row array where the rows appear in the same ordered as in the referred range.

To return the last 3 rows in the reverse order, from bottom to top, change the order of the row_num arguments like this:

=CHOOSEROWS(A4:D13, -1, -2, -3)Excel CHOOSEROWS function to extract certain rows from array (4)

Extract every other row from an array in Excel

To get every other row from a given range, use CHOOSEROWS in combination with a few other functions. The formula will slightly vary depending on whether you are extracting odd or even rows.

To return odd rows such as 1, 3, 5, … the formula takes this form:

=CHOOSEROWS(A4:D13, SEQUENCE(ROUNDUP(ROWS(A4:D13)/2, 0), 1, 1, 2))

To return even rows such as 2, 4, 6, … the formula goes as follows:


Excel CHOOSEROWS function to extract certain rows from array (5)

How this formula works:

In essence, the CHOOSEROWS function returns rows based on an array of sequential odd or even numbers generated by the SEQUENCE function. A detailed formula break-down follows below.

Firstly, you determine how many rows to return. For this, you employ the ROWS function to get the total number of rows in the referenced array, which you divide by 2, and then round the quotient upward or downward to the integer with the help of ROUNDUP or ROUNDDOWN. As this number will later be served to the rows argument of SEQUENCE, rounding is needed to get an integer in case the source range contains an odd number of rows.

As our source range has an even number of rows (10) that is exactly divided by 2, both ROUNDUP(10/2, 0) and ROUNDDOWN(10/2, 0) return the same result, which is 5.

The returned number is served to the SEQUENCE function.

For odd rows:

SEQUENCE(5, 1, 1, 2)

(Video) CHOOSEROWS, CHOOSECOLS to Extract, Select & EXPAND to Add Rows & Columns from Table Array In Excel

For even rows:

SEQUENCE(5, 1, 2, 2)

The SEQUENCE formula above produces an array of numbers consisting of 5 rows and 1 column, starting at 1 for odd rows (at 2 for even rows), and incremented by 2.

For odd rows, we get this array:


For even rows, we get this one:


The generated array goes to the row_num1 argument of CHOOSEROWS, and you get the desired result:

=CHOOSEROWS(A4:D13, {1;3;5;7;9})

Reverse the order of rows in an array

To flip an array vertically from top to bottom, you can also use the CHOOSEROWS and SEQUENCE functions together. For example:


In this formula, we set only the first argument (rows) of SEQUENCE, which equals the total number of rows in the initial array ROWS(A4:D13). The omitted arguments (columns, start, step) default to 1. As a result, SEQUENCE produces an array of sequential numbers such as 1, 2, 3, …, n, where n is the last row in the source array. To make CHOOSEROWS count rows in the down-up direction, the generated sequence is multiplied by -1, so the row_num argument gets an array of negative numbers such as {-1;-2;-3;-4;-5;-6;-7;-8;-9;-10}.

As a result, the order of items in each column is changed from top to bottom:Excel CHOOSEROWS function to extract certain rows from array (6)

Extract rows from multiple arrays

To get specific rows from two or more non-contiguous ranges, you first combine them using the VSTACK function, and then pass the merged range to CHOOSEROWS.

For example, to extract the first two rows from the range A4:D8 and the last two rows from the range A12:D16, use this formula:

(Video) TOROW & TOCOL Functions in Excel to Convert Data Set or Array From Columns to Rows & Rows to Columns

=CHOOSEROWS(VSTACK(A4:D8, A12:D16), 1, 2, -2, -1)Excel CHOOSEROWS function to extract certain rows from array (7)

Get rows based on a string containing row numbers

This example shows how to return particular rows by extracting the numbers from an alpha-numeric string.

Suppose you have comma-separated numbers in cell G3 listing the rows of interest. To extract the row numbers from a string, use the TEXTSPLITfunction that can split a text string by a given delimiter (comma in our case):


The result is an array of text values such as {"3","5","7","10"}. To convert it to an array of numbers, perform any math operation that does not change the values, say +0 or *1.

=TEXTSPLIT(G3, ",") *1

This produces the numeric array constant {3,5,7,10} that the CHOOSEROWS function needs, so you embed the TEXTSPLIT formula in the 2nd argument:


As a result, all the specified rows are returned as a single array:Excel CHOOSEROWS function to extract certain rows from array (8)

CHOOSEROWS function not working

If the CHOOSEROWS formula results in an error, it's most likely to be one of these reasons.

#VALUE! error

Occurs if the absolute value of any row_num argumentis zero or higher than the total number of rows in the array.

#NAME? error

Occurs if the function's name is misspelled or the function is not supported in your Excel. Currently, CHOOSEROWS is only available in Excel 365 and Excel for the web.

#SPILL! error

Occurs when there are not enough blank cells to fill with the results. To fix it, just clear the obstructing cells.For more information, please see Excel #SPILL! error.

That's how to use the CHOOSEROWS function in Excel to return particular rows from a range or array. Thank you for reading and I hope to see you on our blog next week!

Practice workbook for download

Excel CHOOSEROWS formula - examples(.xlsx file)

(Video) 9 Exciting NEW Excel Functions for Shaping Arrays - Incredible!

You may also be interested in

  • TAKE function to get rows or columns from array
  • DROP function to remove rows or columns from array
  • EXPAND function to grow array to specified dimensions


How do I extract specific rows in Excel? ›

Extract rows that meet criteria with Kutools for Excel
  1. Select the column you extract rows based on, and click Kutools > Select > Select Specific Cells.
  2. Then in the Select Specific Cells dialog, check Entire row option in Selection type, and specify your criteria in the Specify type section.
Nov 16, 2020

How do you extract all rows from a range that meet criteria in one column? ›

5. Extract all rows from a range that meet the criteria in one column [Excel defined Table]
  1. Select a cell in the dataset.
  2. Press CTRL + T.
  3. Press with left mouse button on check box "My table has headers".
  4. Press with left mouse button on OK button.

How do I extract specific rows from a matrix? ›

Direct link to this answer
  1. To extract any row from a matrix, use the colon operator in the second index position of your matrix. For example, consider the following:
  2. “row1” is the first row of “A”, and “row2” is the second row.
  3. For more on basic indexing, see:

How do I select certain rows in Excel based on value? ›

Highlight Rows Based on a Number Criteria
  1. Select the entire dataset (A2:F17 in this example).
  2. Click the Home tab.
  3. In the Styles group, click on Conditional Formatting.
  4. Click on 'New Rules'.
  5. In the 'New Formatting Rule' dialog box, click on 'Use a formula to determine which cells to format'.

Can you use Xlookup with multiple criteria? ›

One of the key new features XLOOKUP brings to the table is the ability to lookup using multiple criteria (without complex array formulas – arrays are now dynamic!).

How do you extract data from Excel based on multiple criteria? ›

To do an Excel lookup with multiple criteria, you can use the INDEX and MATCH functions.
  1. The INDEX function can return a value from a specific place in a list.
  2. The MATCH function can find the location of an item in a list.
Jan 7, 2023

How do I extract multiple values from an array? ›

Slicing an Array

To extract only a subset of the array, use the array_slice( ) function: $subset = array_slice (array , offset , length ); The array_slice( ) function returns a new array consisting of a consecutive series of values from the original array.

How do you extract something from an array? ›

Extract Elements From Arrays
  1. Double-click Extract Elements from Arrays in the Tasks list.
  2. Draw a connector between an array input node, and the Extract Elements from arrays node. ...
  3. Click the button in the Extract Elements from Array node.

How do you extract data from an array? ›

If you want to remove an item from an array, you can use the pop() method to remove the last element or the shift() method to remove the first element.

What is Fieldvalue function in Excel? ›

You can use the FIELDVALUE function to retrieve field data from linked data types like the Stocks or Geography data types. There are easier methods for writing formulas that reference data types, so the FIELDVALUE function should be used mainly for creating conditional calculations based on linked data types.

How do you use group function in Excel? ›

On the Data tab, in the Outline group, click Group. Then in the Group dialog box, click Rows, and then click OK. Tip: If you select entire rows instead of just the cells, Excel automatically groups by row - the Group dialog box doesn't even open. The outline symbols appear beside the group on the screen.

How do I expand a list of data types in Excel? ›

To open the data types gallery, go to the Data tab in Excel > Data Types group > expand the dropdown.

How can I get specific rows? ›

Steps to Select Rows from Pandas DataFrame
  1. Step 1: Gather your data. ...
  2. Step 2: Create a DataFrame. ...
  3. Step 3: Select Rows from Pandas DataFrame. ...
  4. Example 1: Select rows where the price is equal or greater than 10. ...
  5. Example 2: Select rows where the color is green AND the shape is rectangle.
May 29, 2021

How do I select specific rows? ›

Or click on any cell in the column and then press Ctrl + Space. Select the row number to select the entire row. Or click on any cell in the row and then press Shift + Space. To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers.

How do you select rows based on values? ›

Pandas: How to Select Rows Based on Column Values
  1. Method 1: Select Rows where Column is Equal to Specific Value df. loc[df['col1'] == value]
  2. Method 2: Select Rows where Column Value is in List of Values. df. ...
  3. Method 3: Select Rows Based on Multiple Column Conditions df. loc[(df['col1'] == value) & (df['col2'] < value)]
Sep 14, 2021

How do you pull data from Excel based on criteria? ›

In Excel pull data from another sheet based on criteria, with these steps:
  1. Go to Sheet2 (see the steps on the video above)
  2. Select a cell in an unused part of the sheet (cell C4 in this example).
  3. On the Excel Ribbon's Data tab, click Advanced.
  4. Choose Copy to another location.
  5. Click in the List Range box.

How do I filter specific rows? ›

Click a cell in the range or table that you want to filter. On the Data tab, click Filter. in the column that contains the content that you want to filter. Under Filter, click Choose One, and then enter your filter criteria.

How do I pull only certain data from a cell in Excel? ›

=LEFT(B1,2) to extract the first 2 characters of the cell B1. =RIGHT(B1,8) to extract the last 8 characters of the cell B1. =MID(B1,4,2) to extract the 2 characters following the 4th character in B1. To apply the changes to the cells below, drag down the blue square.

What are the limitations of Xlookup? ›

Does Xlookup have a limit? The Xlookup function doesn't have a limit. This means you can use all 1,048,576 rows and 16,384 columns of a workbook.

What is better than Xlookup? ›

VLOOKUP defaults to the closest match whereas XLOOKUP defaults to an exact match.

Is VLOOKUP still better compare to Xlookup? ›

XLOOKUP is much more flexible than VLOOKUP, which can look up values only in the leftmost column of a table, and return values from corresponding columns on the right, as we saw in the example above. In contrast, the XLOOKUP model requires simpler steps and can return values for any column in either direction.

How do I extract multiple rows from unique values in Excel? ›

4 Methods to Extract Unique Values
  1. Go to Data tab in the menu.
  2. In Sort and Filter box, Click Advanced button.
  3. Choose "Copy to another location"
  4. In "List range :" box, select a range from which unique values need to be extracted (including header)
  5. In "Copy to :" box, select a range in which final output to be put.

How do I create a dynamic selection in Excel? ›

Creating a Dynamic Drop Down List in Excel (Using OFFSET)
  1. Select a cell where you want to create the drop down list (cell C2 in this example).
  2. Go to Data –> Data Tools –> Data Validation.
  3. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.

How do you pull data from another spreadsheet based on a criteria in? ›

How to Pull Data From Another Sheet in Excel Using Cell References
  1. Click in the cell where you want the pulled data to appear.
  2. Type = (equals sign) followed by the name of the sheet you want to pull data from. ...
  3. Type ! ...
  4. Press Enter.
  5. The value from your other sheet will now appear in the cell.
May 20, 2022

How does Xlookup work in Excel? ›

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

Can you do an index match with multiple criteria? ›

INDEX MATCH with multiple criteria enables you to do a successful lookup when there are multiple lookup value matches. In other words, you can look up and return values even if there are no unique values to look for.

How do I pull multiple rows in Xlookup? ›

XLOOKUP to return multiple columns or rows

You enter the formula in the top-left cell of the results range, and Excel automatically spills the results into adjacent blank cells. In our case, the return array (B2:D7) includes 3 columns (Date, Item and Amount), and all three values are returned into the range G2:I2.

How do you split an array into multiple parts? ›

To divide an array into two, we need at least three array variables. We shall take an array with continuous numbers and then shall store the values of it into two different variables based on even and odd values.

How do you split an array into 4 parts? ›

Given an array of n non-negative integers. Choose three indices i.e. (0 <= index_1 <= index_ 2<= index_3 <= n) from the array to make four subsets such that the term sum(0, index_1) – sum(index_1, index_2) + sum(index_2, index_3) – sum(index_3, n) is maximum possible.

What is extract () function used for? ›

The extract() function imports variables into the local symbol table from an array. This function uses array keys as variable names and values as variable values. For each element it will create a variable in the current symbol table. This function returns the number of variables extracted on success.

Which allows to extract data from array and objects into separate variables? ›

Destructuring is a JavaScript expression that makes it possible to unpack values from arrays, or properties from objects, into distinct variables. That is, we can extract data from arrays and objects and assign them to variables.

How do you fetch a single element from an array? ›

You should use Array. find . From MDN: The find() method returns the value of the first element in the array that satisfies the provided testing function.

How do you access data in an array? ›

Access Array Elements

You can access an array element by referring to its index number. The indexes in NumPy arrays start with 0, meaning that the first element has index 0, and the second has index 1 etc.

How do I extract an index from an array? ›

To find the position of an element in an array, you use the indexOf() method. This method returns the index of the first occurrence the element that you want to find, or -1 if the element is not found.

How do you map data from an array? ›

The syntax for the map() method is as follows: arr. map(function(element, index, array){ }, this); The callback function() is called on each array element, and the map() method always passes the current element , the index of the current element, and the whole array object to it.

What is Besseli function in Excel? ›

Besseli is a function that is used in Excel to calculate the Bessel function of the first kind. This function is used to calculate the function values at specific points in a range of numbers.

What is DMAX function in Excel? ›

Description. Returns the largest number in a field (column) of records in a list or database that matches conditions you that specify.

What is Mduration function in Excel? ›

MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

How do you use subgroups in Excel? ›

Select one subset of data as you desire to include in a group and then click on the Group command in the Data tab of Excel Ribbon. To create another group with another subset of data, select the rows and then either click on the Group command or use this shortcut key Shift + Alt + Right Arrow.

Does Excel have a Groupby function? ›

Group a column by using an aggregate function

For more information see Create, edit, and load a query in Excel. Select Home > Group by. In the Group by dialog box, select Advanced to select more than one column to group by. To add another column, select Add Grouping.

How do you expand grouped rows in Excel? ›

To expand a group, click the plus sign (+) next to the group. You can also double-click the minus sign or the plus sign to collapse or expand all groups at once.

How do I expand certain rows in Excel? ›

Resize rows
  1. Select a row or a range of rows.
  2. On the Home tab, select Format > Row Width (or Row Height).
  3. Type the row width and select OK.

How do you auto expand rows? ›

Select the row or rows that you want to change. On the Home tab, in the Cells group, click Format. Under Cell Size, click AutoFit Row Height. Tip: To quickly autofit all rows on the worksheet, click the Select All button, and then double-click the boundary below one of the row headings.

How do I extract only certain data from a cell in Excel? ›

Depending on where you want to start extraction, use one of these formulas:
  1. LEFT function - to extract a substring from the left.
  2. RIGHT function - to extract text from the right.
  3. MID function - to extract a substring from the middle of a text string, starting at the point you specify.
Oct 4, 2022

How do I extract only some data from a cell in Excel? ›

=LEFT(B1,2) to extract the first 2 characters of the cell B1. =RIGHT(B1,8) to extract the last 8 characters of the cell B1. =MID(B1,4,2) to extract the 2 characters following the 4th character in B1. To apply the changes to the cells below, drag down the blue square.

How do I extract only part of a cell in Excel? ›

Here are four methods you can use to extract a substring in Excel:
  1. Use the LEFT, RIGHT and MID functions. You can use the LEFT, RIGHT and MID functions to extract specific text from a cell. ...
  2. Use the TRIM function. ...
  3. Use the MID and FIND functions. ...
  4. Use Flash Fill.
Dec 14, 2021

How do I separate data from multiple rows in Excel? ›

Split cells
  1. Click in a cell, or select multiple cells that you want to split.
  2. Under Table Tools, on the Layout tab, in the Merge group, click Split Cells.
  3. Enter the number of columns or rows that you want to split the selected cells into.

How do I select specific data in Excel without dragging? ›

Or click on any cell in the column and then press Ctrl + Space. Select the row number to select the entire row. Or click on any cell in the row and then press Shift + Space. To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers.

How do I extract part of a cell content? ›

Here is how to do this:
  1. Select the cells where you have the text.
  2. Go to Data –> Data Tools –> Text to Columns.
  3. In the Text to Column Wizard Step 1, select Delimited and press Next.
  4. In Step 2, check the Other option and enter @ in the box right to it. ...
  5. In Step 3, General setting works fine in this case. ...
  6. Click on Finish.

How do I remove partial data from multiple cells in Excel? ›

To remove specific text from each cell in a selected range, press Ctrl + H to display the Find and Replace dialog, and then:
  1. Enter the unwanted text in the Find what box.
  2. Leave the Replace with box blank.

How do I separate data from different rows? ›

1. First we will select the range of cells consisting of data that we want to split in multiple rows. 2. Next, we will go to data tab and select the option of text to columns and then we will check the delimiter used in data and then we will press enter key.

What is the use of Excel slicer function? ›

Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed. You can use a slicer to filter data in a table or PivotTable with ease.

How do I separate rows between groups in Excel? ›

Select the rows you want to ungroup. Go to the Data tab > Outline group, and click the Ungroup button. Or press Shift + Alt + Left Arrow which is the Ungroup shortcut in Excel. In the Ungroup dialog box, select Rows and click OK.


1. CHOOSEROWS a New Function How To Use in Excel
(CTF Mentor)
2. Select All Cells with a Specific Value - Excel Trick
3. How to use ChooseRows in Microsoft Excel #shorts
(Giuliano De Luca)
(Analytics Atlas)
5. How To Use The TAKE And DROP Functions In Excel
(Excel Bytes)
6. CHOOSEROWS function in excel - New excel formula in 2022
(Excel (A-Z))
Top Articles
Latest Posts
Article information

Author: Fredrick Kertzmann

Last Updated: 01/16/2023

Views: 5855

Rating: 4.6 / 5 (46 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Fredrick Kertzmann

Birthday: 2000-04-29

Address: Apt. 203 613 Huels Gateway, Ralphtown, LA 40204

Phone: +2135150832870

Job: Regional Design Producer

Hobby: Nordic skating, Lacemaking, Mountain biking, Rowing, Gardening, Water sports, role-playing games

Introduction: My name is Fredrick Kertzmann, I am a gleaming, encouraging, inexpensive, thankful, tender, quaint, precious person who loves writing and wants to share my knowledge and understanding with you.