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

 

[Jonmo1]

I think that is the equivelent of entering

=$A$1:$A$6621 into a cell, say B1
In B1, it returns the value of A1 (relevant to the row it's entered in)
In B2, it reutns the value of A2

It's Excel "trying" to do it's best to figure out what it's supposed to do with an invalid reference..

 

[Andrew Poulsom]

And =INDEX([xxx.xlsm]ComplCampAll!$A$1:$A$6621,0,1) returns a reference to $A$1:$A$6621. The zero means all rows.

 

[Aladin Akyurek]

And =INDEX([xxx.xlsm]ComplCampAll!$A$1:$A$6621,0,1) returns a reference to $A$1:$A$6621. The zero means all rows.

And Excel will display often the first value from that reference.

 

[TomCon]

Thanks much for the explanations. Its good to know more and more of Excel's "special" behaviors.



I interpret the summary of the above as follows.

• When you use INDEX() with row=0, with 'array' in the same workbook, it will return #VALUE.
• When you use INDEX() with 'array' in a different workbook, if row=0, it is interpreted as a reference to the entire array, which will often be displayed as the value in the same row of the array as the row in the calling INDEX() function.

Why it was desinged this way is of course an interesting question to speculate about, but always good to understand.

Thanks for your time to respond!

Tom

 

[Aladin Akyurek]

Thanks much for the explanations. Its good to know more and more of Excel's "special" behaviors.




I interpret the summary of the above as follows.

• When you use INDEX() with row=0, with 'array' in the same workbook, it will return #VALUE.
• When you use INDEX() with 'array' in a different workbook, if row=0, it is interpreted as a reference to the entire array, which will often be displayed as the value in the same row of the array as the row in the calling INDEX() function.

Why it was desinged this way is of course an interesting question to speculate about, but always good to understand.

Thanks for your time to respond!

Tom

=INDEX(A2:F200,MATCH(X2,A2:A200,MatchType),0)

means all rows, that is, all cells of the column calculated by the MATCH bit.

=INDEX(A2:F200,MATCH(X2,A1:F1,MatchType),0)

means all columns, that is, all cells of the row calculated by the MATCH bit.

=INDEX(A2:A4,0)

will return (a) A2 if entered in B2, A3 if entered in B3, A4 if entered in B4, and (b) #VALUE! if entered outside ROWS corresponding to A2:A4.

In Case (a), we still have all cells of A2:A4, but the row that houses the formula is used the row index. In Case (b), there is no row index to use. Hence, we get #VALUE!, indicative of an array.

 

[fsamyn]

=INDEX(A2:F200,MATCH(X2,A2:A200,MatchType),0)

means all rows, that is, all cells of the column calculated by the MATCH bit.

=INDEX(A2:F200,MATCH(X2,A1:F1,MatchType),0)

means all columns, that is, all cells of the row calculated by the MATCH bit.

=INDEX(A2:A4,0)

will return (a) A2 if entered in B2, A3 if entered in B3, A4 if entered in B4, and (b) #VALUE! if entered outside ROWS corresponding to A2:A4.

In Case (a), we still have all cells of A2:A4, but the row that houses the formula is used the row index. In Case (b), there is no row index to use. Hence, we get #VALUE!, indicative of an array.

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.

 

[RoryA]

Also note that this is only a problem with row=0 not a problem when column=0, in which case Excel returns correctly #VALUE

Not necessarily. As with the row case, it depends entirely on the data passed to the index function and where the formula is. If the data is one row and multiple columns and the formula is in a cell below one of those cells, then using 0 as the column number has the same effect.

Whether it is a bug or undocumented feature is moot, in my opinion.

 

[Jonmo1]

Whether it is a bug or undocumented feature is moot, in my opinion.

I wouldn't call it a Bug OR a Feature...
I would call it "Expected behavior"

 

[RoryA]

I definitely would not call it expected. I would expect to get the first value of the array every time unless I array entered the formula into multiple cells.