How to Use the SORTBY Function to Sort Arrays by Multiple Criteria in Excel

When you are auditing complex, multi-dimensional datasets in Microsoft Excel, relying on the standard Data Sort button is structurally fragile because it physically mutates the source data. If you must generate a mathematically pristine, sorted copy of an array based on multiple competing criteria (e.g., sort by Department alphabetically, then by Salary descending) without touching the original matrix, you must deploy the dynamic SORTBY function.

Understanding the Multi-Tier Sorting Architecture

The SORTBY function (exclusive to modern Office 365 environments) is a dynamic array compiler. Unlike the simpler SORT function, SORTBY does not require the sorting criteria to actually exist within the output array. It can intercept a target payload and sort it based on an entirely independent, external vector, supporting up to 126 distinct sorting tiers.

The syntax requires careful structuring: =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

Executing the Sorting Vector

Imagine you have a master database. Employee Names are in A2:A100, Departments are in D2:D100, and Salaries are in G2:G100. You must generate a new array containing just the Employee Names, but they must be sorted first by Department (A-Z), and then within each Department, by Salary (highest to lowest).

To execute the precise sorting sequence, click a pristine cell (e.g., J2) and type the precise command:

=SORTBY(A2:A100, D2:D100, 1, G2:G100, -1)

The exact millisecond you press Enter, the Excel engine intercepts the payload.

• It loads the target array (A2:A100) into active RAM.
• It analyzes Tier 1: D2:D100, 1. It maps the employees to their departments and forces an ascending (1) alphabetical sort.
• It analyzes Tier 2: G2:G100, -1. For any employees trapped in the same department node (e.g., “Engineering”), it evaluates their salaries and forces a descending (-1) numerical sort.
• The engine violently rearranges the memory pointers without ever touching columns A, D, or G.
• It drops the final, mathematically perfected payload into a vertical spill array starting at J2. Because SORTBY is entirely dynamic, if a user updates a salary in column G, the master sorted array in column J will instantaneously recalculate and re-sort itself in real-time.