Quick answer
The TAKE formula returns the first or last rows or columns from a range.
=TAKE(SORT(A2:D100,4,-1),10)Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=TAKE(array, rows, [columns])When to use it
Lookup formulas connect tables together. Dynamic array formulas go further by returning lists, tables, and spill ranges that update automatically when the source data changes.
- Return a price from a product table
- Find an employee name from an ID
- Filter open orders into a report
- Create sorted or unique lists
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Lookup value such as SKU, ID, or name |
| F:G | Reference table with the match column and return column |
| B | Returned result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Take the first 10 rows
=TAKE(A2:D100,10)Return only the first rows of a larger list.
Take the last 5 rows
=TAKE(A2:D100,-5)Useful for recent records when the latest rows are at the bottom.
Take top 10 after sorting
=TAKE(SORT(A2:D100,4,-1),10)Create a top 10 list.
Take first 3 columns
=TAKE(A2:F100,,3)Return only the first columns of a wider table.
Take from filtered data
=TAKE(FILTER(A2:D100,D2:D100="Open"),10)Limit a filtered report to a manageable size.
Handle too-few rows safely
=IFERROR(TAKE(A2:D5,10),A2:D5)Avoid an error when a report has fewer rows than expected.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- The spill area is blocked by existing values below or to the right of the formula cell.
- The source ranges do not have compatible row or column sizes.
- The formula returns an empty result because no rows match the condition.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the TAKE formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.