# How to use the TAKE function

The TAKE function returns a given number of rows or columns from a 2D cell range or array.

Dynamic array formula in cell B8:

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

#### Table of Contents

## 1. TAKE Function Syntax

TAKE(*array*, *rows*, [*columns*])

## 2. TAKE Function Arguments

array |
Required. The source cell range or array. |

rows |
Required. The number of contiguous rows to return, a negative number returns contiguous rows from the end. |

[columns] |
Optional. The number of contiguous columns to return, a negative number returns contiguous rows from the end. |

## 3. TAKE Function example

The picture above shows how the TAKE function returns the two first rows from cell range B2:D5.

Dynamic array formula in cell B9:

### 3.1 Explaining formula

#### Step 1 - TAKE function

TAKE(*array*, *rows*, [*columns*])

#### Step 2 - Populate arguments

*array - B2:D5*

*rows - 2*

[*columns*] -

#### Step 3 - Evaluate function

TAKE(B2:D5, 2)

becomes

TAKE({89, 27, 26;68, 84, 98;19, 92, 62;37, 63, 45}, 2)

and returns

{89, 27, 26;

68, 84, 98}

## 4. TAKE Function example - return columns

The image above demonstrates how to return columns using the TAKE function.

Formula in cell B6:

## 5. TAKE Function example - return rows and columns

The image above demonstrates how to return both rows and columns using the TAKE function. Note that it only returns the intersection of the chosen rows and columns, see the image below.

Formula in cell B9:

TAKE(*array*, *rows*, [*columns*])

## 6. TAKE Function example - negative values

The TAKE function lets you use negative arguments, this means that the function returns rows/columns from the end.

Formula in cell B9:

TAKE(*array*, *rows*, [*columns*])

## 7. TAKE function alternative

It is possible to return the two first rows using the OFFSET function, however, the OFFSET function is volatile and may slow down your worksheet if you use many of them.

Array formula in cell B9:

This array formula should work in most Excel versions.

### 7.1 How to enter an array formula

- Double press with left mouse button on with the left mouse button on cell B9, a prompt appears.
- Type or copy/paste the formula to cell B9.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys.

The formula now has leading and trailing curly brackets, don't enter these characters yourself. They appear automatically.

### 7.2 Explaining formula in cell B6

#### Step 1 - OFFSET function

The OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference.

OFFSET(*reference*, *rows*, *columns*, [*height*], [*width*])

#### Step 2 - Populate arguments

*reference - B2:D5*

*rows - *

*columns - *

[*height*] - 2

[*width*] - 3

#### Step 3 - Evaluate OFFSET function

OFFSET(B2:D5,,,2,3)

becomes

OFFSET({89, 27, 26;68, 84, 98;19, 92, 62;37, 63, 45},,,2,3)

and returns

{89, 27, 26; 68, 84, 98}.

## 8. TAKE 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

=WRAPCOLS(SORTBY(TOROW(TAKE(B2:D5,2)),RANDARRAY(,COLUMNS(TOROW(TAKE(B2:D5,2))))),ROWS(TAKE(B2:D5,2)))

#### Step 1 - Extract two first rows

TAKE(B2:D5, 2)

becomes

TAKE({89, 27, 26;68, 84, 98;19, 92, 62;37, 63, 45}, 2)

and returns

{89, 27, 26;68, 84, 98}.

#### 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(TAKE(B2:D5,2))

becomes

TOROW({89, 27, 26;68, 84, 98})

and returns

{89, 27, 26, 68, 84, 98}.

#### Step 3 - Count cells

The COLUMNS function returns the number of columns in a given cell range or array.

COLUMNS(*array*)

COLUMNS(TOROW(TAKE(B2:D5,2)))

becomes

COLUMNS({89, 27, 26, 68, 84, 98})

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(TAKE(B2:D5,2))

becomes

ROWS({89, 27, 26; 68, 84, 98})

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(TAKE(B2:D5,2))))

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(TAKE(B2:D5,2)),RANDARRAY(,COLUMNS(TAKE(B2:D5,2))))

becomes

SORTBY({89, 27, 26, 68, 84, 98}, {0.215398134613085, 0.390607168196479, ... ,0.83231474462401})

and returns

{84, 89, 26, 27, 98, 68}.

#### Step 7 - Rearrange values to 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(TAKE(B2:D5,2)),RANDARRAY(,COLUMNS(TOROW(TAKE(B2:D5,2))))),ROWS(TAKE(B2:D5,2)))

becomes

WRAPCOLS({84, 89, 26, 27, 98, 68},ROWS(TAKE(B2:D5,2)))

becomes

WRAPCOLS({84, 89, 26, 27, 98, 68}, 2)

and returns

{84, 89, 26; 27, 98, 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(TAKE(B2:D5,2)),RANDARRAY(,COLUMNS(TOROW(TAKE(B2:D5,2))))),TOROW(TAKE(B2:D5,2)))

becomes

LET(z,TAKE(B2:D5,2),x,TOROW(z),WRAPCOLS(SORTBY(x,RANDARRAY(,COLUMNS(x))),ROWS(z)))

## 9. TAKE Function - multiple source ranges

The picture above shows a formula that merges three non-contiguous cell ranges and return values from the two first columns.

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 - Wrap values to rows

TAKE(VSTACK(B3:D6, F3:H6, J3:L6), , 2)

becomes

TAKE({"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}, , 2)

and returns

{"Peach", 43;

"Blueberry", 39;

"Apple", 46;

"Grapefruit", 14;

"Mandarin", 29;

"Raspberry", 33;

"Plum", 25;

"Mango", 37;

"Pear", 17;

"Orange", 31;

"Lime", 17;

"Kiwi", 45}

## Functions in this article

### Functions in 'Array manipulation' category

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

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form