Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.




I use both functions a lot, I get correct results, however I don't understand the reason(s) for using one compared to the other.

Any insights?



1) What did you mean by offset is more versatile?

2) My largest Excel is about 4.7 MB, and uses a lot of OFFSET, but recalc seems instantaneous (I have a very fast computer)

3) See attached .xls - it is just a example sort of thing to compare different methods - the part I still don't get at all, is why are there 2 types of INDEX, namely Reference and Array, and why does the Reference version (cell D31) returns #REF! (??)



1. By more flexible I meant that the cell indexed in the Index function has to be within the specified range, whereas with Offset the function works from the top left cell of the given range, so you don't need to know the size of the data range. You could enter just the single top left cell, and Offset will return the value of any cell within the spreadsheet, including those with negative offset values. The other thing is that Index will return a single cell, or a single column or row from the specified table range, whereas offset can return a range of any specified size. The latter feature I use quite a lot in VBA, but rarely on the spreadsheet.

2. The main difference in speed is that Offset is volatile, which means that it will recalc every time anything changes anywhere in the spreadsheet, whereas index will only result in a recalc if a value is changed within the specified Index range. If your recalc speed is near enough to instantaneous that you don't notice it, then there is no reason not to use Offset.

3. I had to look up the Reference version. I think the given "help" is awful (even by MS help standards). By the "reference" format they seem to mean that you can list multiple ranges, inside brackets, and select one of those ranges with the third index value. The index values still have to be inside the specified range, so if you enter a single cell as the "reference", the index values would have to be 1,1,1 (or just 1 will work). If you have a single range specified then the "reference" version is exactly the same as the "array" version (unless I'm missing something).

It seems that the ranges all have to be on the same sheet. That's pretty poor, Quattro Pro and Lotus 123 had true 3D functionality more than 20 years ago!

Doug Jenkins
Interactive Design Services


OK, I get it now.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close