Sort by custom list

To sort a list in a custom order, you can combine the SORTBY function with the MATCH function. In the example shown, The table is being sorted by the "group" column using in the order shown in cells J5:J7. The formula in D5 is: =SORTBY(B5:D14,MATCH(D5:D14,custom,0)) where custom is the named range J5:J7 that defines desired sort order.

April 14, 2025 · 1 min · 71 words · Catherine Turner

Table of Contents

Explanation

In this example, we are sorting a table with 10 rows and 3 columns.

The goal is to sort the table using values in the Group column in this same custom order.

This array is passed into the SORTBY function as theby_array1argument.

Excel formula: Sort by one column

The range or array used to sort does not need to appear in results.

MATCH supports approximate and exact matching, andwildcards(* ?)

Often, MATCH is combined with the…

Excel formula: Sort by two columns

Excel formula: Random sort

Excel formula: Sort text by length

Excel SORTBY function

Excel MATCH function