TAKE function
TAKE returns the first or last rows or columns of an array, such as the top 10 after sorting. Use negative numbers to take from the end.
Example
Read it as a sentence: keep the first 2 rows of B1:E7 and show the top left value, which gives Item.
Excel shows Item
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
Syntax
TAKE(array, rows, [columns])
Arguments in [square brackets] are optional.
-
array(required) - Source array.
-
rows(required) - Rows to keep; positive from the top, negative from the bottom.
-
columns(optional) - Columns to keep; positive from the left, negative from the right.
Returns: A slice of the array.
One TAKE formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=TAKE({1,2,3},,2) | 1 | Array |
=TAKE(Items!B1:E7,2) | Item | First rows |
=TAKE(Items!B1:E7,-2,-1) | 1.75 | From end |
=TAKE(Items!B1:E7,,2) | Item | Columns only |
=TAKE(Items!B1:E7,20) | Item | More than exist |
=TAKE(Items!B1:E7,"1") | Item | Text count |
=TAKE(Items!B1:E7,0) | #CALC! | Zero rows |
The Items sheet these formulas read
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
Watch out for
- Use negative numbers for the end of the array.
- Leave rows empty (TAKE(a,,2)) to take columns only.
- Asking for more than exists returns the whole array.
Where it works
- Excel
- TAKE arrived in Excel 2024. Older versions do not have it. 365, Excel for the web and Excel 2024; not in older perpetual versions.
- Google Sheets
- No direct equivalent: `=ARRAY_CONSTRAIN(range,rows,cols)`. No known direct TAKE in Sheets (unsure if added recently); use ARRAY_CONSTRAIN.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .