Excel functions / Dynamic arrays
TAKE function
Keeps the first or last rows or columns of an array.
Syntax
=TAKE(array, rows, [columns])
Arguments
array: The array to take from.rows: How many rows: positive from the top, negative from the bottom.columns(optional): How many columns: positive from the left, negative from the right.
When to use it
TAKE gives back the first rows of a range, or the last ones when the number is negative. A third argument does the same for columns.
On its own it is a quick "top of the list". After SORT it becomes a top-N: sort by sales, largest first, then take 3.
Examples
The first two rows
A B C D E F G 1 Rep Region Sales 2 Ana North 1200 Ana North 1200 3 Ben South 800 Ben South 800 4 Chloe North 900 5 Dev West 400 In E2:
=TAKE(A2:C5, 2)→ Ana, North, 1200; Ben, South, 800The last row
A B C D E F G 1 Rep Region Sales 2 Ana North 1200 Dev West 400 3 Ben South 800 4 Chloe North 900 5 Dev West 400 In E2:
=TAKE(A2:C5, -1)→ Dev, West, 400The top two by sales
A B C D E F G 1 Rep Region Sales 2 Ana North 1200 Ana North 1200 3 Ben South 800 Chloe North 900 4 Chloe North 900 5 Dev West 400 In E2:
=TAKE(SORT(A2:C5, 3, -1), 2)→ Ana, North, 1200; Chloe, North, 900SORT puts the largest sales first, and TAKE keeps the first two rows of that.
Common mistakes
- Asking for 0 rows.
=TAKE(A2:C5, 0)has nothing to return and gives #CALC!. - Expecting an error for too many. Asking for more rows than there are returns all of them, with no error.
- Columns without rows. To take columns only, leave the rows argument empty:
=TAKE(A2:C5, , 1)is the first column.
Common questions
How do I get the top 5 rows in Excel?
Sort, then take: =TAKE(SORT(A2:C100, 3, -1), 5) sorts by the third column, largest first, and returns the first five rows.
TAKE in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
Practise it
Related functions
- DROP: Removes the first or last rows or columns of an array.
- CHOOSEROWS: Returns the rows you name from an array, in the order you name them.
- SORT: Sorts a range. The result spills.
- FILTER: Keeps only the rows that meet a condition. The result spills.
- INDEX: The value at a given row and column of a range.