Seeking explanation of INDEX function

[deadlyduck]

Hi,

I'm looking for the reason why the following use of the INDEX function returns a cell address [ie the formula evaluates SUM($C$2:C3)]-

Please Login or Register  to view this content.

whereas this 'basic' INDEX function

Please Login or Register  to view this content.

will return the (more normal) value of whatever item is in position 2 of the C2:C8 range.

Excel's help system doesn't point out this behaviour.

I've seen the technique used in a couple of instances (usually pretty complex functions!)-sometimes in place of volatile functions- and would be keen to be able to identify situations where I could suggest it.

Thanks in advance for any replies!

[NBVC]

The Help file kind of does show a sample of this. The help file splits it's description into 2 sections, in Array Form and in Reference Form...

When the Index() function is incorporated into another function within a formula, it returns the Index or actual range of the results to work with rather than the actual results. This is the Reference form.

The array form is your first example, where the INDIRECT is the main function of the formula and your are looking to return the result of that intersection of Row/Column.

[daddylonglegs]

I believe that the distinction between reference form and array form refer to the first argument of INDEX, it can be an array or a reference.

In the reference form the behaviour you describe is explicitly described in Excel help (in mine at least for 2003), i.e. this section

The result of the INDEX function is a reference and is interpreted as such by other formulas. Depending on the formula, the return value of INDEX may be used as a reference or as a value. For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL function uses the return value of INDEX as a cell reference. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in cell B1

Note also that you can use INDEX to return a reference to a whole column or row if the opposite argument is zero

[deadlyduck]

Thank you both for your responses- I've looked again at the Help system and saw the examples I missed earlier (using 2007 version). [As an aside, I wish that the Help files would replace the term 'reference' with either 'address' or even 'cell address'.... maybe it's just me but I find that term far less ambiguous than 'reference' ]

My Neanderthal understanding at present is that if the INDEX function is nested/ enclosed within another function (particularly statistical/mathematical functions- I haven't yet been able to make it do this with a logical function) that it will return a cell address.

[daddylonglegs]

Reference can be either a single cell or a range, for instance this formula

=INDEX(A1:J10,0,4)

returns the whole column D1:D10

You can see that if you nest it in a sum function

=SUM(INDEX(A1:J10,0,4))

on your original question, it's all about the context. If you use INDEX in a formula that expects a reference, it'll use the reference, if a value is expected it'll use the value,

best example might be an OFFSET formula where you can get both options in the same function, e.g.

=OFFSET(INDEX(A1:A10,5),0,0,INDEX(A1:A10,5))

The same INDEX function delivers the cell reference [A5] for the first argument of OFFSET and also the height argument [defined by the value in A5]

[deadlyduck]

on your original question, it's all about the context. If you use INDEX in a formula that expects a reference, it'll use the reference, if a value is expected it'll use the value,

Thanks DLL- I think that explanation encapsulates it perfectly. Case solved