Explanation
In this example, the goal is to extract the first 3 values or the last 3 values from the named range data (B5:B15). We also want to exclude any empty cells from our results. In the worksheet shown the formula in cell D5 is:
=INDEX(FILTER(data,data<>""),SEQUENCE(3,1,1,1))
Working from the inside out, we use the SEQUENCE function to construct a row number value for INDEX like this:
SEQUENCE(3,1,1,1)
We are asking SEQUENCE for an array of 3 rows x 1 column, starting at 1, with a step value of 1. The result is an array like this:
{1;2;3}
which is returned directly to the INDEX function as the row_num argument:
=INDEX(FILTER(data,data<>""),{1;2;3})
To construct the array for INDEX, we use the FILTER function to retrieve a list of non-blank entries from the named range data (B5:B15) like this:
FILTER(data,data<>"")
The array argument is data, and the include argument is the expression data<>"". This can be translated literally as “return values from the named range data where values in data are not empty”. The result is an array with 9 values like this:
{"Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis"}
Notice values associated with the two empty cells have been removed. This array is returned to the INDEX function as its array argument.
Finally, INDEX returns the 1st, 2nd, and 3rd values from the array returned by FILTER:
{"Atlanta";"Chicago";"Dallas"}
Last n values
To get the last n values with FILTER, you use the same formula structure, with the inputs to SEQUENCE modified to construct a “last n” array of row numbers. For example, to get the last 3 non-blank values in the example shown, you can use a formula like this:
=INDEX(FILTER(data,data<>""),SORT(SEQUENCE(3,1,SUM(--(data<>"")),-1)))
The main trick here is counting the non-blank entries in the named range data like this:
SUM(--(data<>""))
We use a double-negative to force the TRUE FALSE values to 1s and 0s, then use the SUM function to get the count. The result is returned as the start argument inside SEQUENCE. We supply -1 for step to step backwards from start .
We also wrap the SORT function around SEQUENCE so the array returned is {7;8;9} and not {9;8;7}. This ensures that values are returned in the same order they appear in the source data.
Explanation
This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE function.
The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range for the first argument ( array ), and a value for n as the second argument ( k ):
=LARGE(range,1) // 1st largest
=LARGE(range,2) // 2nd largest
=LARGE(range,3) // 3rd largest
In this example, the logic for FILTER is constructed with this snippet:
score>=LARGE(score,3)
which returns TRUE when a score is greater than or equal to the 3rd highest score. Since there are 12 cells in the range D5:D16, this expression returns an array of 12 TRUE and FALSE values in an array like this:
{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
This array is returned directly to the FILTER function as the include argument, where it is used to filter the data. Only rows where the result is TRUE make it into the final output. The result from the formula in F5 includes the 3 top scores in the data, which spill into the range F5:H7.
Sort results by score
By default, FILTER will return matching records in the same order they appear in the source data. To sort results in descending order by score , you can nest the original FILTER formula inside the SORT function like this:
=SORT(FILTER(data,score>=LARGE(score,3)),3,-1)
Here, FILTER returns results directly to the SORT function as the array argument. sort_index is set to 3 (score) and sort_order is set to -1, for descending order.
