Very strange result for INDEX() with row=0; help?

[TomCon]

I have an index function call, where array is in another workbook (open one), and the rowref for the INDEX call is another cell. The formula is then filled down.

So, it looks like this:
=INDEX([xxx.xlsm]ComplCampAll!$A$1:$A$6621,Q2,1)
(so, the above fills down to Q2, Q3, Q4...)

In all cases, the returned value appears to be correct to me, EXCEPT when the value in Column Q (which is the row argument to the INDEX() call) is equal to 0 (zero). In that case, i expect #VALUE to be the result. But instead i get an actual result.

In fact, what is returned a value retrieved from the array, in the row in the array that is equal to the row number of the cell in calling sheet.

For example, if in row 8 i have
=INDEX([xxx.xlsm]ComplCampAll!$A$1:$A$6621,Q8,1)
and if the value in cell Q8 is 0, i get a result as if the call were as follows:
=INDEX([xxx.xlsm]ComplCampAll!$A$1:$A$6621,8,1)
...and, that in all such cases.

As a further test, i tried INDEX() with the array on the same worksheet, and in all cases i constructed, where row=0, i get #VALUE for the result.

So, does anybody have advice here? Is there a known defect when the array argument to INDEX() is in a different workbook, or anything special i need to do in that case?

Thanks for any advice on this very confusing situation!
Tom

 

[Aladin Akyurek]

When using INDEX with row=0 and the formula on one of the rows of the called array Excel return the content of that cell
When using INDEX with row=0 and the formula is outside of the rows of the called array then Excel returns #VALUE. The same should have happened in the previous case.

This is not a feature of Excel trying to supply information when none is needed but simply a bug. Also note that this is only a problem with row=0 not a problem when column=0, in which case Excel returns correctly #VALUE

In essence, the formula results are different based on the location of the formula with respect to the array. This is not an undocumented feature but a flaw.

A simple way of avoiding it is by using this =INDEX(array, if(row=0,"",row), column) or by moving the formula outside the array rows.

Re-cap:

1)

INDEX(Range,0,N)

means all of the cells (rows) of the Nth column in Range:

Ex.

=INDEX(A2:B4,0,1)

will deliver the values of A2:A4. If this formula is entered in a single cell, say, E2, you'll see just the first item from A2:A4.

2)

INDEX(Range,M,0)

means all of the cells (columns) of the Mth row in Range:

Ex.

=INDEX(A2:B4,2,0)

will deliver the values of A3:B3. If this formula is entered in a single cell, say, F2, you'll see just the first item from A3:B3.

3. On the other hand:

=INDEX(C2:C4,K)

will default to C2 if K = 0.

A remedy would be:

=IF(K = 0, "", INDEX(C2:C4,K))

 

[Jonmo1]

I would "expect" =INDEX(A1:A10,0) to work the same as =A1:A10

Right?

Now take this
=VLOOKUP($A$1:$A$10,$D$1:$E$100,2,FALSE)

It works as long as the formula is entered within the Rows 1-10.
And it uses the value in A1:A10 Relative to the row the formula is entered in.

Right?

 

[RoryA]

Ex.

=INDEX(A2:B4,0,1)

will deliver the values of A2:A4. If this formula is entered in a single cell, say, E2, you'll see just the first item from A2:A4.

Yes, but the issue is that if you enter it into E3 instead of E2 you will see the second value, not the first, and if you enter it in E8 you will get an error.

 

[RoryA]

I would "expect" =INDEX(A1:A10,0) to work the same as =A1:A10

Agreed. My issue is that I would not expect either to work the way they do. If I cut and paste (or drag) a cell with a formula, I don't generally expect its value to change based on where I put it, which is what can happen with this behaviour.

 

[Aladin Akyurek]

Yes, but the issue is that if you enter it into E3 instead of E2 you will see the second value, not the first, and if you enter it in E8 you will get an error.

Where you enter something like INDEX(A2:B4,0,1) might modify what you get to see. That behavior makes you think in terms of a bug. Offset shows a similar behavior if I recall it right. The idea is of course to feed it to another function that needs the specified array or enter the specified array in a compatible range.

 

[Jonmo1]

Agreed. My issue is that I would not expect either to work the way they do. If I cut and paste (or drag) a cell with a formula, I don't generally expect its value to change based on where I put it, which is what can happen with this behaviour.

I'll agree with that as well, it's poor practice to make a formula that works dependant on the location of that formula..
Like depending on ActiveCell or ActiveSheet in VBA.

But, considering it's known and accepted that =A1:A10 Does Indeed behave that way,
then it's equally expected that =INDEX(A1:A10,0) behaves the same way.

 

[Jonmo1]

Where you enter something like INDEX(A2:B4,0,1) might modify what you get to see. That behavior makes you think in terms of a bug. Offset shows a similar behavior if I recall it right. The idea is of course to feed it to another function that needs the specified array or enter the specified array in a compatible range.

Yes,
=INDEX(A1:A10,0) should NOT be used by itself, it should only be used in conjuction with another function, like
=SUM(INDEX(A1:A10,0))


So I think this comes down to Excel trying to do what it thinks you want it to do...

 

[shg]

I think it's just an extension of the implied intersection that Excel does in more obvious circumstances.

Name row 1 x, column A y, add some numbers to each. In B2 and copy down and across, =x*y

 

[RoryA]

I think it's an inferred intersection - I never implied it.

 

[shg]

I'm implying you inferred you did