The TAKE function is one of 14 functions added to Excel in the spring of 2022. It displays the contents of the first or last X number of rows or columns from a range.
In columns A:H in the screenshot below are the current standings in the English Premier League table. Using Power Query to connect to the BBC Sports webpage and setting the Query Refresh to “Refresh Data When Opening the File” means that it will always be up-to-date.
The TAKE function in K3 (which being a dynamic array function spills to R6) “takes” the data from the first 4 rows of A2:H21. The function in K3 is =TAKE(A2:H21,4)
The TAKE function in K14 “takes” the data from the last 3 rows of A2:H21. The function in K14 is =TAKE(A2:H21,-3)
When the source data (A2:H21) is updated, K3:R6 and K14:R16 automatically update so for example, if Manchester United move up to 4th position, their “playing record” will move to A5:H5 and will also be displayed in K6:R6
In this video I provide several examples to show you how it works, including how to use it with unsorted data. Links to the video and individual sections below…
NOTE: If you don’t see the TAKE function in your Excel, it’s only available to users on Microsoft 365 Beta Channel