Explanation
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is copied down:
COUNTA(list)+ROW(list)-ROW()
- COUNTA(list) returns the count of non-blank items in the list (10 in the example)
- ROW(list) returns the starting row number of list (5 in the example)
- ROW() returns the row number the formula resides in
The result of this expression is a single number starting at 10, and ending at 1 as the formula is copied down. The first formula returns the 10th item in the list, the second formula returns the 9th item in the list, and so on:
=INDEX(list,10+5-5,1) // item 10
=INDEX(list,10+5-6,1) // item 9
=INDEX(list,10+5-7,1) // item 8
etc.
With Dynamic Arrays
Excel 365 supports dynamic array formulas, which can used to create a simpler and more efficient formula. The SORTBY function can perform a "reverse sort" with help from the SEQUENCE function. The formula in D5 should be:
=SORTBY(list,SEQUENCE(ROWS(list),1,ROWS(list),-1))
Inside the SEQUENCE function, the ROWS function is used twice to get a count of rows in the range. The first count is used as the rows argument in sequence, the second count is used for the start argument. The step argument is supplied as -1, so that the array returned by sequence starts at 10 and counts down to 1. The result is delivered to SORTBY as by_array1:
=SORTBY(list,{10;9;8;7;6;5;4;3;2;1})
With this configuration, SORTBY sorts the named range list in reverse order and the results spill into the range D5:D14.
Related formulas
Basic numeric sort formula
Random sort formula
Sort and extract unique values
Reverse text string
Related functions
INDEX Function
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....
COUNTA Function
The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.
ROW Function
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
SORTBY Function
The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array. The range or array used to sort does not need to appear in results.
SEQUENCE Function
The Excel SEQUENCE function generates a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional, determined by rows and columns arguments.
