There is a list of numbers in column A in an Excel worksheet. I would like to display these numbers in column C in order.
Column C should update automatically when column A is modified, without needing to manually reorder anything.
There is a list of numbers in column A in an Excel worksheet. I would like to display these numbers in column C in order.
Column C should update automatically when column A is modified, without needing to manually reorder anything.
EDIT: Thanks to AFH, the formula can be simplified to just the SMALL()
function!
This can be done with a very simple normal formula in a Table:
The formula in C2:C8
is:
=SMALL(A:A,ROW()-ROW($1:$1))
A Table is required so that entering a value just below the bottom of the table, i.e. in A9
, will cause the formula in column C
to be extended downwards.
The Table can be dispensed with if only existing data will be modified, or if you are comfortable extending the formula by hand, or if you are prepared to pre-fill it past the current end of the data.
=IFERROR(SMALL(A:A,ROW()-1),"")
works equally well. What's the advantage of adding MATCH()
and INDEX()
? And ROW($1:$1)
always returns 1, so why not use the literal, which can be incremented if there are more header lines?
– AFH
Jun 13 at 11:37
INDEX()
/MATCH()
at all and there's no advantage to having them. (I'm so used to more complex formula where they are required, that I forgot that for this simple case they weren't needed.) The reason for using ROW($1:$1)
is precisely so that if more header rows are added (or rows inserted above the Table) after the formula has been entered, the Table won't break and you don't need to adjust a literal value to fix it. It makes the Table pretty much bullet-proof. I've updated the answer with an even simpler version. I hope you don't mind.
– robinCTS
Jun 13 at 12:01