Explanation

The SORTBY function can sort values in a range with an array that doesn’t exist on the worksheet.

In this example, we want to sort the values in B5:B15 by the number of characters each string contains. Working from inside out, we use the LEN function to get the length of each value:

LEN(B5:B15) // get length of all strings

Because we give LEN an array with 11 values, we get an array with 11 lengths:

{5;7;14;6;5;13;9;4;8;6;11}

Each number represents the character length of a value in B5:B11.

This array is returned directly to the SORTBY function as the by_array1 argument:

=SORTBY(B5:B15,{5;7;14;6;5;13;9;4;8;6;11},-1)

The SORTBY function allows sorting based on one or more “sort by” arrays, as long as dimensions are compatible with the source data. In this case, there are 11 rows in the source data, and 11 rows in the array returned by LEN, so the requirement is met.

The SORTBY function uses the array of lengths returned by LEN to sort the values in B5:B15, and returns sorted results to D5 in a dynamic array . Because the sort order is set to -1, the values are sorted in reverse (descending) order by length. Use a positive 1 to sort in ascending order.

Dynamic Array Formulas are available in Office 365 only.

Explanation

The SORT function sorts a range using a given index, called sort_index . Normally, this index represents a column in the source data.

However, the SORT function has an optional argument called " by_col " which allows sorting values organized in columns. To sort by column, this argument must be set to TRUE, which tells the SORT function that sort_index represents a row.

In this case, we want to sort the data by Score, which appears in the second row, so we use a sort_index of 2. The SORT function that appears in C8 is configured like this:

=SORT(C4:L5,2,-1,TRUE)
  • array is the data in the range C4:L5
  • sort_index is 2, since score is in the second row
  • sort_order is -1, since we want to sort in descending order
  • by_col is TRUE, since data is organized in columns

The SORT function returns the sorted array into the range C8:L9. This result is dynamic; if any scores in the source data change, the results will automatically update.

With SORTBY

The SORTBY function can also be used to solve this problem. With SORTBY, the equivalent formula is:

=SORTBY(C4:L5,C5:L5,-1)

Dynamic Array Formulas are available in Office 365 only.