Limiting the Number of Results from a Function

Isaac notes that many of the functions available in the latest versions of Excel return an array of values, which means that the results "spill" into multiple cells. Often the functions will return too many results for his needs. Isaac wonders, therefore, if there is a way to tell Excel to return, say, only five or six values when multiple values are returned.

There are three relatively easy ways to accomplish this task. Let's say, though, that Isaac is using the SORT function, like the following, to sort the values in the range A2:A150:

=SORT(A2:A150)

You could use just about any other array-producing function desired, but SORT works good to illustrate how you can limit what is returned. The function returns a sorted array of the values in the specified range. That means that it "spills over" to fill 149 cells with the results. Isaac, though, would only like to have the first six results from the array.

The first approach is to wrap the SORT function in the CHOOSEROWS function, in this manner:

=CHOOSEROWS(SORT(A2:A150),1,2,3,4,5,6)

The CHOOSEROWS function returns the specified rows from an array. In this case, the formula specifies that rows 1 through 6 should be returned. If desired, you could throw the SEQUENCE function into the mix:

=CHOOSEROWS(SORT(A2:A150),SEQUENCE(6))

Since the SEQUENCE function returns an array consisting of however many sequential numbers you specify, it is the same as explicitly stating that you want the first six rows returned by the SORT function.

Closely related to CHOOSEROWS is the TAKE function, which can be used to return both rows and columns from an array. If the array contains only a single column—as is the case with the SORT function—then you can simply specify the number of rows desired:

=TAKE(SORT(A2:A150),6)

One advantage to using TAKE is that you can specify whether you want rows or columns from the end of the array. So, for instance, if Isaac wanted the last six rows in the array, then he could use this:

=TAKE(SORT(A2:A150),-6)

The difference here is that the parameter indicating the number of rows is negative.

The third approach is to rely on the INDEX function, which is very similar to using the CHOOSEROWS function:

=INDEX(SORT(A2:A150),SEQUENCE(6))

The difference between INDEX and CHOOSEROWS in this usage is that INDEX requires the use of the SEQUENCE function because it expects an array indicating the rows to return. (In other words, you cannot provide an explicit list of rows as you can with CHOOSEROWS.)

Which of these approaches should you use? It really depends on the version of Excel you are using. All of the functions used in this tip will work with Excel in Microsoft 365. The oldest availability is the INDEX function, which was introduced all the way back in Excel 2003. Both the SORT and SEQUENCE functions are first available in Excel 2021, and both CHOOSEROWS and TAKE in Excel 2024.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10144) applies to Microsoft Excel 2021, 2024, and Excel in Microsoft 365.