Excel: INDIRECT(ADDRESS(...)) vs. OFFSET(...)

Question

It seems that using Excel functions INDIRECT(ADDRESS(...)) and OFFSET(...) can be used interchangeably.

For example, the following two formulas return the same result to an absolute reference:

= INDIRECT(ADDRESS(1,1))

= OFFSET(<current cell>,1-ROW(),1-COLUMN())

And similarly the next two formulas return the same result to a relative reference (in this case, for example, these formulas return whatever value is in the cell below this cell with the formula):

= OFFSET(<current cell>,1,0)

= INDIRECT(ADDRESS(ROW()+1,COLUMN()))

My questions are: Is one method always preferred over the other? It seems to me that INDIRECT(ADDRESS(...)) lends itself to more absolute type references and OFFSET(...) lends itself to more relative type references, but as shown above, either method could be used to accomplish either type of reference. Or, is there a completely different alternative to using these two functions that is superior to both of these options?

Answer 1

Neither are preferred as both are volatile functions and too many of them will affect the calc times.

Volatile function recalc every time that the application recalcs, even if the underlying data has not changed.

Use INDEX instead it is non volatile:

=INDEX($1:$1048576,ROW()+1,COLUMN())

This will only recalc when the data to which it refers changes.

Use INDIRECT only when the sheet is the variable.

OFFSET can almost always be replaced with INDEX.