Should it be Index or Offset ?

colwyn · 10-01-2008, 09:50 AM

=INDEX('2007'!A:N,K2,14)
=OFFSET('2007'!A1,K2-1,13)

Anyone know which of the above works fastest when applied down 50,000+ rows of several spreadsheets?
Thanks.
Colwyn.

**Ron Coderre** · 10-01-2008, 09:56 AM

The OFFSET function is "volatile"....meaning that it recalculates
whenever any cell in the workbook calculates.

The INDEX function is non-volatile and only recalcs when one of it's
referenced values changes. Consequently, INDEX is faster.

The list of volatile functions includes:
RAND, NOW, TODAY, OFFSET, CELL, INDIRECT

I hope that helps.