Lookup & Reference

TAKE Function in Excel

Returns a specified number of rows or columns from the start or end of an array.

Syntax

  • =TAKE(array, rows, [columns])

Arguments

  • array (required): Array to take from
  • rows (required): Number of rows (negative for from end)
  • columns (optional): Number of columns (negative for from end)

Examples

  • =TAKE(A1:D10, 5) - First 5 rows - Result: 5 rows
  • =TAKE(A1:D10, -3) - Last 3 rows - Result: 3 rows

When to use TAKE

  • Use TAKE when you need to retrieve values from a table or range based on a key.
  • Confirm data types match (text vs number) and trim spaces before lookups.
  • Pair with [IFERROR](/functions/iferror/) or [IFNA](/functions/ifna/) for cleaner reports.
  • See the [lookup functions guide](/guides/excel-lookup-functions-guide/) for VLOOKUP, XLOOKUP, and INDEX MATCH paths.

Common TAKE mistakes

  • #N/A often means the key is missing or exact match is wrong — see [#N/A error](/errors/na/).
  • Lock table references with $ when copying formulas down a column.
  • Compare legacy vs modern lookups in [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Common errors

  • Requires Excel 365

Use cases

  • Get top/bottom records
  • Subset data
  • Dynamic ranges

Frequently asked questions

  • How do I get the top 10 values from sorted data? Use =TAKE(SORT(data, 1, -1), 10) to sort descending and take top 10. Or =TAKE(SORTBY(data, sort_column, -1), 10) to sort by a specific column. Negative sort order = descending.
  • How do I get the last N rows of a table? Use negative rows: =TAKE(A1:D100, -5) returns the last 5 rows. This is dynamic - as data grows, it always returns the most recent 5 rows. Great for showing latest entries.
  • Can I use TAKE with both rows and columns? Yes! =TAKE(A1:Z100, 10, 3) returns the first 10 rows and first 3 columns. =TAKE(A1:Z100, -5, -2) returns the last 5 rows and last 2 columns. Mix positive and negative as needed.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.