Function Problems

How to Sort Dynamic Arrays by Multiple Columns with SORTBY in Excel

Natalie Taylor Oct 9, 2026 868 views

Question details

The user needs to sort a variable-length dynamic array, created using functions like VSTACK and FILTER, by two or more columns.

How to Sort Dynamic Arrays by Multiple Columns with SORTBY in Excel

Product: Excel
Device & OS: not provided
Scenario: Sorting a dynamically generated in-memory data array by multiple criteria (columns) when standard worksheet column references cannot be used.
Observed behavior: Requires a formula combination that correctly targets specific columns within the dynamic array to act as sorting keys.

Before you start

Ensure your version of spreadsheet software supports modern dynamic array functions. You will need access to LET, SORTBY, and CHOOSECOLS functions to complete this process.

Solution 1Recommended

Use LET, SORTBY, and CHOOSECOLS to Sort the Array

This method uses the LET function to store the generated array in memory, then extracts specific columns using CHOOSECOLS to serve as sorting keys in the SORTBY function.

When dealing with dynamic arrays created by VSTACK or FILTER, the data resides in memory rather than directly on the grid. This means you cannot use standard column references (like C:C) for sorting. The CHOOSECOLS function solves this by extracting the exact column needed from the in-memory array.

Define the dynamic array with LET

Start your formula by using the LET function to name your array. For example, type `=LET(data, VSTACK(array1, array2), ...)` so that 'data' stores your combined array.

Set up the SORTBY function

Next in the LET formula, introduce the SORTBY function, referencing your named array: `=LET(data, VSTACK(...), SORTBY(data, ...))`.

Extract sorting columns using CHOOSECOLS

For the sort key arguments in SORTBY, use CHOOSECOLS. For the first sort key, type `CHOOSECOLS(data, 3)` to sort by the 3rd column, followed by `1` for ascending order.

Add secondary sort criteria

Add additional CHOOSECOLS functions for subsequent keys. Your final formula will look like this: `=LET(data, VSTACK(...), SORTBY(data, CHOOSECOLS(data, 3), 1, CHOOSECOLS(data, 4), -1))`.

Use LET, SORTBY, and CHOOSECOLS to Sort the Array

Formula Efficiency: Using the LET function not only makes the formula easier to read but also improves calculation performance by preventing Excel from calculating the VSTACK or FILTER array multiple times.

Process Dynamic Arrays Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions, allowing you to manipulate complex data sets using SORTBY, CHOOSECOLS, and LET seamlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet where you want to perform the array calculations.
2. Enter the dynamic formula: Select an empty cell and enter the combined `=LET(..., SORTBY(...))` formula to handle your multi-column sorting.
3. Spill the results: Press Enter. The sorted data will automatically spill into the adjacent cells based on the size of your dynamic array.

Fully compatible with Microsoft Excel's dynamic array functions like SORTBY and VSTACK.Handle complex multi-column sorting quickly without needing macros or VBA.Lightweight software with lightning-fast calculation speeds for large data arrays.

Frequently Asked Questions

Why can't I use standard column references in SORTBY for dynamic arrays?

Because a dynamic array (generated by VSTACK or FILTER) exists in the application's memory before it spills onto the worksheet grid. Standard column letters like C:C refer to the grid itself, not the array. You must use CHOOSECOLS or INDEX to isolate the sorting column from the array.

What does the LET function do in this formula?

The LET function allows you to assign a name (such as 'data') to the result of a formula. In this scenario, it prevents the system from having to recalculate the heavy VSTACK or FILTER function multiple times within the SORTBY and CHOOSECOLS arguments.

Can I sort by more than two columns using SORTBY?

Yes, SORTBY allows you to specify multiple sort keys. You can sort by three or more columns simply by adding additional CHOOSECOLS functions and specifying the sort order (1 for ascending, -1 for descending) for each one.

Excel & Data Problems