Excel: What's the best way to get the value of a Relative Row but from a Fixed Column?

Question

The following formula will always return the value of the 4th column (D) of the next row.

=INDIRECT("R[1]C[" & 4-COLUMN() & "]",FALSE)

Is there a better way to achieve the same results?

Answer 1

Depends what you mean by better :-)

If you mean shorter/simpler and A1 style, then shoover's answer is fine:

=INDIRECT("$D"&ROW()+1)

If you prefer R1C1 style (easier to read IMHO ;-) ) then an even shorter/simpler/faster solution is:

=INDIRECT("R[1]C4",)

However, if you're after the fastest solution, or just simply prefer a non-volatile one, then a Named Formula is the way to go:

• Define a Name, say Col4Down1, and set its value to:

  =INDEX(!$D:$D,ROW()+1)

• Place the following formula in a cell to get the desired result:

  =Col4Down1

This works because of a little known quirk when using the bang operator ! in a Named Formula. When you don't specify a sheetname, !$D:$D always refers to the fourth column irrespective of column deletions/insertions. Think of it as absolute-absolute addressing.

Finally, Lance Roberts' answer, whilst being non-volatile, suffers from a couple of problems. As he mentions, it will only work in certain, predetermined, rows. Secondly, insertion/deletion of any columns from A-D, or of any rows above, will break it. Modifying it to fix these leads to the following (if entered into cell B2):

=INDEX(B:B:2:2,ROW()+1,4)

or if you prefer R1C1 style and have set your spreadsheet to use this style:

=INDEX(R:C,ROW()+1,4)

Answer 2

Similar, but less wordy and easier to read (IMHO), is the A1 style of addressing:

=INDIRECT("$D" & ROW()+1)

Answer 3

This will work if you know the range:

=INDEX($A$1:$E$4, ROW()+1, 4)

Answer 4

Putting a '$' character in front of a column letter or row number will lock it down when you copy and paste.

Eg:

$C$17