Excel TAKE function to extract rows or columns from array

TAKE function in Excel

The Excel TAKE function extracts the specified number of contiguous rows and/or columns from the array or range.

The syntax is as follows:

Where:

Array (required) - the source array or range.

Rows (optional) - the number of rows to return. A positive value takes rows from the start of the array and a negative value from the end of the array. If omitted, columns must be set.

Columns (optional) - the number of columns to return. A positive integer takes columns from the start of the array and a negative integer from the end of the array. If omitted, rows must be defined.

Here's how the TAKE function looks like:

How to use TAKE function in Excel

To align expectations and reality when using the TAKE function in your worksheets, take notice of the following things:

1. The array argument can be a range of cells or an array of values returned by another formula.
2. The rows and columns arguments can be positive or negative integers. Positive numbers take a subset of data from the start of the array; negative numbers - from the end.
3. The rows and columns arguments are optional, but at least one of them should be set in a formula. The omitted one defaults to the total number of rows or columns in the array.
4. If the rows or columns value is greater than there are rows or columns in the source array, all rows / columns are returned.
5. TAKE is a dynamic array function. You enter the formula in only one cell and it automatically spills into as many neighboring cells as needed.

Excel TAKE formula examples

Now that you have a general understanding of how the TAKE function works, let's look at some practical examples to illustrate its real value.

How to take rows / columns from multiple ranges

In situation when you want to extract some columns or rows from several non-contiguous ranges, it takes two steps to accomplish the task:

1. Combine multiple ranges into one vertically or horizontally using the VSTACK or HSTACK function.
2. Return the desired number of columns or rows from the combined array.

Depending on the structure of your worksheet, apply one of the following solutions.

Stack ranges vertically and take rows or columns

Let's say you have 3 separate ranges like shown in the image below. To append each subsequent range to the bottom of the previous one, the formula is:

=VSTACK(A4:C6, A10:C14, A18:C21)

Nest it in the array argument of TAKE, specify how many rows to return, and you will get the result you are looking for:

=TAKE(VSTACK(A4:C6, A10:C14, A18:C21), 4)

To return columns, type the appropriate number in the 3^rd argument:

=TAKE(VSTACK(A4:C6, A10:C14, A18:C21), ,2)

The output will look something like this:

TAKE function alternative for Excel 2010 - 365

In Excel 2019 and earlier versions where the TAKE function is not supported, you can use OFFSET as an alternative. Though the OFFSET formula is not as intuitive and straightforward, it does provide a working solution. And here's how you set it up:

1. For the 1^st argument, supply the original range of values.
2. The 2^nd and 3^rd arguments or both set to zero or omitted, assuming you are extracting a subset from the beginning of the array. Optionally, you can specify how may rows and columns to offset from the upper-left cell of the array.
3. In the 4^th argument, indicate the number of rows to return.
4. In the 5^th argument, define the number of columns to return.

Summing up, the generic formula takes this form:

For instance, to extract 6 rows and 2 columns from the start of the range A3:C14, the formula goes as follows:

=OFFSET(A3:C14, , , 6, 2)

In all versions except Excel 365 and 2021 that handle arrays natively, this only works as a traditional CSE array formula. There are two ways to enter it:

• Select the range of cells the same size as the expected output (6 rows and 2 columns in our case) and press F2 to enter Edit mode. Type the formula and press Ctrl + Shift + Enter to enter it in all the selected cells at once.
• Enter the formula in any empty cell (E3 in this example) and press Ctrl + Shift + Enter to complete it. After that, drag the formula down and to the right across as many rows and columns as needed.

The result will look similar to this:

Excel TAKE function not working

In case a TAKE formula does not work in your Excel or results in an error, it's most likely to be one of the below reasons.

Practice workbook for download

Excel TAKE formula - examples (.xlsx file)