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
- TAKE Function Syntax
- TAKE Function Arguments
- TAKE Function example
- TAKE Function example - return columns
- TAKE Function example - return columns and rows
- TAKE Function example - negative values
- TAKE Function alternative
- TAKE Function - values in random order
- TAKE Function - multiple source ranges
- Get Excel *.xlsx file
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}
Useful resources
TAKE function - Microsoft support
Excel TAKE function to extract rows or columns from array
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 signs
Use 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 Oscar
You can contact me through this contact form