We can drag this formula to the following cells in column B. To make sure the list remains unchanged while dragging, we have used $ here.
The generic formula is as follows :
The list in our case is A$2:A$9
How this formula works :
The heart of this formula is the INDEX function, in which the list has been provided as an array argument.
The second argument of the INDEX function is the below expression :
- COUNTA(list) returns the count of non-blank items in the list (8 in the example)
- ROW(list) returns the starting row number of list (2 in the example)
- ROW() returns the current row number, the formula resides in
The result of the above expression is a single number starting at 8, and ending at 1 as the formula is copied down.
Now the INDEX function in the formula resides in first cell(B2) returns the 8th item in the list(A2:A9), the second formula(B3) returns the 7th item in the list, and so on:
=INDEX(list,8+2-2,1) // item 8
=INDEX(list,8+2-3,1) // item 7
=INDEX(list,8+2-4,1) // item 6