The name “list” is a named range B5:B14. Named ranges are absolute references by default, so be sure to use an absolute reference if not using a named range. The second part of the formula is an expression that works out the correct row number as the formula is copied down: 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:
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: 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: With this configuration, SORTBY sorts the named range list in reverse order and the results spill into the range D5:D14.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
