TED #040: Array of Possibilities: Introducing =TAKE() in Excel
Learn how this function can revolutionize your data handling experience in Excel.
In the world of Excel, dynamic arrays are unsung heroes that make our data dance. Simply put, they allow us to work with a set of values, or an array, at once rather than one value at a time. And the =TAKE() function? It lets us extract a specific number of values from an array, either vertically or horizontally. At first glance you might think, what’s the big deal? But once you learn about how to use it and then nest it with other DAF’s, you will find that it can be really helpful.
The syntax
Lets start where we always do - by breaking down the syntax. Here we go:
=TAKE(array, rows, [columns])
array: The array you want to extract values from.
rows: The rows you want to extract from the array. Use a positive number to start from the top, a negative to start from the bottom.
[columns]: The columns you want to extract from the array. Use a positive number to start from the left, a negative to start from the right.
A simple example
In this example we have a list of sales reps and their performance over the last 4 quarters. Here they are:
We want to grab the bottom 4 results on the list. Here is the syntax:
=TAKE(A2:C17, -4)
Nest with =SORTBY()
Lets take it a step further. What if we want the top 4 results based on sales? To accomplish this we want to first sort the list by the number of sales. We then want to pluck the top 4 results. We can do this by nesting =TAKE() with =SORTBY(). Here is the formula:
=TAKE(SORTBY(A2:C17, C2:C17,-1), 4)
Note: learn more about using Sort & Sortby viewing my newsletter on the topic. Find it here.
Nest with =FILTER()
In this last example we want to know the top 2 sales quarters for the rep of our choosing. In this case we want to use a pull down menu to choose the rep we would like to see the results for. Here is the formula:
=TAKE(SORT(FILTER(A2:C17, A2:A17=E2),3,-1), 2)
Thanks for reading this newsletter. I have found the =TAKE() function to be extremely practical in grabbing information from any data table! I think you will to. Practice does make perfect so be sure to try out the examples we covered today. To help you with that I created sample data for you to use. Here is a link so that you can follow along:
When you’re ready, there are a few other ways I can help you master Excel:
Do you have any burning questions about Excel that you want answered? Send your question to ajay@theexceldojo.com. I will address it in a future newsletter.
Do you or your organization need a little spreadsheet help? Book a 1 on 1 with me here.
Also, please be sure to follow me on your favorite social media platforms! It’s with your support that I can continue to provide you with this content!
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you found this newsletter informative please share with a friend or two...
Is there any news letter, how can I reach you, please share mail id