In How to Use Excel’s LET Function, I showed several examples of a powerful function that Microsoft added to Excel 365 in the summer of 2020. In this article, I’ll show you how to use the LET function with Excel’s relatively new feature, dynamic arrays.
If you want to follow along with the explanation, you can download the example file here.
Specifically, we’ll use the LET function to report any number of top or bottom results, dynamically.
What’s a Dynamic Array?
Microsoft introduced dynamic arrays for Excel 365 in 2018 and 2019—depending on your 365 subscription. This feature fundamentally changed the way that Excel works with arrays. Until then, with Control-Shift-Enter (CSE) arrays, we selected the cell or cells into which an array would be displayed. And then we entered the array using CSE.
But now, with dynamic arrays, we can enter array formulas normally, and they will “spill” into as many empty cells as necessary to display the complete array.
These tables illustrate the SORTBY function in action, where the Sorted Table shows the Table1 data sorted by Sales. I didn’t set up the Sorted Table in an Excel Table because dynamic arrays—returned by SORTBY in this case—don’t work inside Excel Tables.
Introducing the SORTBY Function
Only two formulas were needed to return the entire Sorted Table. I entered these formulas in the cells directly below the two labels shown:
Prod: =SORTBY( Table1[Prod], Table1[Sales], -1 )
Sales: =SORTBY( Table1[Sales], Table1[Sales], -1 )
The SORTBY function also could return both columns and ten rows with this single formula:
=SORTBY( Table1, Table1[Sales], -1 )
And here’s the syntax for the SORTBY function…
=SORTBY( Array, ByArray1, SortOrder1, [ByArray2], [SortOrder2], … , … )
Array: The range or array to sort.
ByArray1: The first array to sort on.
SortOrder1: The sort order, where 1 = ascending order (the default), and -1 = descending order.
Optionally, you can enter additional pairs of ByArray and SortOrder arguments.
How to Return Sections of a Column of Values
Suppose you now want to return only the top and bottom number of results from Table1, a number specified in the RowsDisp cell shown here.
That’s where the fun comes in!
To begin, let’s think about the challenge we face. The easiest way to use formulas to sort our data is to use the SORTBY function—a function that returns an array. And therefore, we must wrap the SORTBY function with some other function that can return different chunks from the array, as shown in the preceding figure.
If the numbers were in one column of your worksheet, we could choose between two functions to return, say, the first three cells in the column:
=INDEX( reference, row_num, column_num )
=OFFSET( reference, rows, cols, height, width )
Specifically, we could write formulas like this:
=INDEX( MyRange, 1 ) : INDEX( MyRange, 3 )
=OFFSET( MyRange, 0, 0, 3 )
But these formulas don’t work with an array. Specifically…
=INDEX( {1;2;3;4;5}, 1) : INDEX( {1;2;3;4;5}, 3 )
… returns a #VALUE! error. That is, the reference operator “:” doesn’t work with arrays.
And Excel doesn’t even allow us to enter:
=OFFSET( {1;2;3;4;5}, 0, 0, 3 )
How to Return Sections of an Array
Let’s expand our possible solutions to this problem by thinking even more about arrays. Here’s one very useful but somewhat obscure Excel feature when we use arrays in formulas:
When a worksheet function expects a single value, but we give it an array instead, the function returns a result for each value in that array.
For example, this formula returns the value 2, as expected:
=INDEX( {2;4;6;8;10}, 1 )
But this formula returns the value 12:
=SUM( INDEX( {2;4;6;9;10}, {1;2;3} ) )
That is, because we used the array {1;2;3} for the INDEX function’s row_num argument, the INDEX function returned the array {2;4;6}, and then the SUM function returned the sum of those numbers, which is 12.
So then, going back to the Sorted Table, shown again here, all we need to do to return arrays of the first three Product and Sales columns is to use formulas like this:
=INDEX( Prod, {1;2;3} )
=INDEX( Sales, {1;2;3} )
But how do we generate those array constants dynamically?
Introducing the SEQUENCE Function
This is where another dynamic array function comes into play: SEQUENCE. Here’s its syntax…
=SEQUENCE( rows, columns, start, step )
…and here are a few examples from the example workbook you can download:
=SEQUENCE(3) = {1;2;3}
=SEQUENCE( 3, 1, 10, -1 ) = {10;9;8}
=SEQUENCE( 3, 1, 5, 1) = {5;6;7}
Using the LET Function to Return Product Names
So now, FINALLY, let’s get to the LET function. Here’s the Table we want to sort, some named cells, and the tables we want to create:
Here are the contents of the named cells:
RowsAll: =ROWS( Table1 )
RowsDisp: 5
Now, let’s begin the LET function…
For the top products, we know we’re going to need the SEQUENCE array, so let’s start with it:
=LET(
Seq, SEQUENCE( RowsDisp ),
ArrayTop, 99,
ArrayTop )
Now we can add the SORTBY array to display the product names:
=LET(
Seq, SEQUENCE( RowsDisp ),
ArrayAll, SORTBY( Table1[Prod], Table1[Sales], -1 ),
ArrayTop, 99,
ArrayTop )
And finally, all we need to do is to use INDEX to return the number of top Product names that we specified in the RowsDisp cell:
=LET(
Seq, SEQUENCE( RowsDisp ),
ArrayAll, SORTBY( Table1[Prod], Table1[Sales], -1 ),
ArrayTop, INDEX( ArrayAll, Seq ),
ArrayTop )
Using the LET Function to Return the Sales Values
To return the top of the Sales values, we can copy the Prod array to the Sales cell and then change only the highlighted value:
=LET(
Seq, SEQUENCE( RowsDisp ),
ArrayAll, SORTBY( Table1[Sales], Table1[Sales], -1),
ArrayTop, INDEX( ArrayAll,Seq ),
ArrayTop )
If we wanted to comment this formula, we could use this variation:
=LET(
Seq, SEQUENCE( RowsDisp ), Note1, “An array like: {1;2;3}”,
ArrayAll, SORTBY( Table1[Sales], Table1[Sales], -1), Note2, “The full sorted Sales”,
ArrayTop, INDEX( ArrayAll,Seq ), Note3, “The top rows of sorted Sales”,
ArrayTop )
Here’s the syntax for the SEQUENCE function again:
=SEQUENCE( rows, columns, start, step )
And here’s the commented formula for the bottom of our sorted array for Sales:
=LET(
Seq, SEQUENCE( RowsDisp, 1, RowsAll – RowsDisp + 1 ), Note1, “An array like: {8;9;10}”,
ArrayAll, SORTBY( Table1[Sales], Table1[Sales], -1), Note2, “The full sorted Sales array”,
ArrayTop, INDEX( ArrayAll, Seq ), Note3, “The bottom rows of sorted Sales”,
ArrayTop )
And then, if we enter a RowsDisp value of 3, here’s our result:
And the formulas that return the labels for the two tables are:
=”Top “&CHOOSE(RowsDisp,”One”, “Two”, “Three”, “Four”, “Five”) &” Items”
=”Bottom “&CHOOSE(RowsDisp,”One”, “Two”, “Three”, “Four”, “Five”) &” Items”
Again, you can download the workbook at this link.
Also, How to Use Excel’s LET Function provides an introduction to the LET function.
