Excel function: INDIRECT

indirect returns the reference that a given address refers to.

The following formula returns the reference to cell D1. Because a reference is displayed as the value of the cell that the reference refers, this formula just displays the value of the cell D1:

=indirect("d1")

Same thing, but use R1C1 reference style:

=indirect("r1c4", false)

Because the returned value is a reference, the following formula evaluates to true:

=isref(indirect("f3"))

The «opposite» function of indirect is address: it turns a reference into a text that identifies the referenced cell.

The following formula evaluates to D1:

=address(indirect("d1"); true; 4)

A convoluted way to display the value of the cell that is above the cell containing a formula:

=indirect(address( row()-1, column() ))