If you need to reverse a list (i.e. put the items in a list or column in reverse order) you can do so using the INDEX function with some help from the COUNTA and ROW functions.
In the example shown, the formula is:
The name “list” is a named range that refers to the range $B$3:$B$12. So, without named ranges, the formula would look like this:
How this formula works
At the heart of this formula is the INDEX function, which is given the entire list as an array. The rest of the formula:
Simply works out one thing: the row number to give to INDEX as the second argument.
- COUNTA(list) always returns the count of non-blank items in the list (10 in the example)
- ROW(list) always returns the starting row number of list (3 in the example)
- ROW() dynamically returns the current row number that the formula resides in
So, the formula is evaluated like this:
=INDEX(list,10+3-3,1) // instance 1 =INDEX(list,10+3-4,1) // instance 2 =INDEX(list,10+3-5,1) // instance 3
ROW() supplies an incrementing number, which is used to build a row number that corresponds to the last item in the list and moves toward the first item.
The first instance generates a row number for the last item in the list, the second instance generates a row number for the second to last item in the list, and so on.