SubscribeReverse a List using Formulas [Using Excel INDEX() Formula]
Posted on November 19th, 2009 in Excel Howtos , Learn Excel - 7 comments
Here is a simple yet novel use of formulas. Let us say you have a list of values in range A1:A5 and you want to reverse the list.
In an empty cell write =INDEX($A$1:$A$5,6-ROWS($A$1:A1)) and copy down.
Boom, you get the reversed list.
Here is how the formula works:
- In the reversed list, first item is last item in the original list (ie 5th item in our case).
- INDEX() formula takes a list, a row number (and optional column number) and returns the value at intersection.
- In this case, $A$1:$A$5 is the list.
- 5 is the size of list.
- ROWS($A$1:A1) gives running numbers from 1 thru 5 when copied in any range of 5 cells. Read more on using ROWS() formula.
More on lists: Shuffle a list of numbers | Sort a list of texts using formulas | Remove duplicates from a list
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
would this be assisted by having a row you include in the range that you would insert above to include new entries within that range (could also name it). Then change the formula slightly so that it becomes:
=INDEX($A$1:$A$5,row(last row ref)-ROWS($A$1:A1))
it would allow you to constantly update the list without having to amend the value within the formula.
Nice post !!! Here’s another way to reverse using OFFSET
=OFFSET($A$1,COUNTIF(A1:$A$5,”")-1,0). The formula refuses to work with blank cells though
Regards,
What about =INDEX($A$1:$A$5,ROWS(A1:A$5))
Hi Chandoo,
Very interesting and useful. To avoid hard-coding the 6 you could use:
=INDEX($A$1:$A$5,COUNTA($A$1:$A$5)-ROWS($A$1:A1)+1)
Hi shandoo,
there a more versatile formula (any location of the list)
First of all, name your list : ie Mylist
close-up in the just right column type :
=INDEX(Mylist , COUNTA(Mylist) + ROW(Mylist) – ROW() , 1)
then copy down (double-click on the copy anckor)
Simon: I would use counta() not row() to find the number of items. This will let you place the values anywhere, not just in first row.
@Ajay: Good one.
@Mauricio: That is awesome. I was trying for something like that, but couldnt think of absolute reference at end, really brilliant stuff…
@Kanti: Yes, you are right. I wanted to keep the formula simple so that our members can understand the technique without worrying about other formulas.
@Modeste: Very good formula.. thanks
Thx your formulas.
But I still use the old method by data sort after fill number on next column.
Thx Chandoo