Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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

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 .