# FILTER on first or last n values

To FILTER and extract the first or last n values (i.e. first 3 values, first 5 values, etc.), you can use the FILTER function together with INDEX and SEQUENCE. In the example shown, the formula in D5 is:

where **data** is the named range B5:B15.

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:

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:

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.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.