I confirm inadequate behavior, in LO 4.1.3.2 on Windows.

Additionally, explicitly using "zero" values for row and/or column (instead of leaving them empty, which should be equivalent to "zero" according to official help), are also resulting in a #value error.

The only argument that is being accepted as optional is the last one ("range"), instead of accepting "row" and "column" as optional (or "zero") too.

I think the issue is the help may need a better explanation.

When the range has more than one row or column, respective default options or zero value need to enter the formula as array Ctrl+Shift+Enter instead of Enter, because it is working with a matrix in this cases.

(In reply to comment #2)
> I think the issue is the help may need a better explanation.
> 
> When the range has more than one row or column, respective default options
> or zero value need to enter the formula as array Ctrl+Shift+Enter instead of
> Enter, because it is working with a matrix in this cases.

Yeah that seems to be the case. Perhaps this can be put forward as a bug to have the Array check-box automatically selected as it is for functions such as MMULT, GROWTH and TREND

If you are agree, please adapt the title and change the importance from normal to enhancement.

(In reply to comment #3)
> Perhaps this can be put forward as a bug to
> have the Array check-box automatically selected as it is for functions such
> as MMULT, GROWTH and TREND

I have to disagree. INDEX is not always an array formula.

When INDEX is used as an array function, the first argument is required and the rest are optional.

When INDEX is used as simple function, both row and column arguments are required, and there are no "default" values for these 2 arguments (not "zero" nor "one").

IMHO, this is a documentation problem only, thus the doc's team should be cc'd and the description of the bug should be changed accordingly.

(In reply to comment #5)
> (In reply to comment #3)
> > Perhaps this can be put forward as a bug to
> > have the Array check-box automatically selected as it is for functions such
> > as MMULT, GROWTH and TREND
> 
> I have to disagree. INDEX is not always an array formula.
> 
> When INDEX is used as an array function, the first argument is required and
> the rest are optional.
> 
> When INDEX is used as simple function, both row and column arguments are
> required, and there are no "default" values for these 2 arguments (not
> "zero" nor "one").
> 
> IMHO, this is a documentation problem only, thus the doc's team should be
> cc'd and the description of the bug should be changed accordingly.

I believe this should be an array formula.
The first argument, 'reference' is always required.
Only as 'array' is a result returned when this parameter is used on its own.
When 'row' is then added both 'simple' and 'array' return results.
Same when 'column' is added with 'row' so that both are present.
Only as 'array' is a result returned with 'row' 0 or omitted and 'column' present.

If you read the ODF spec. for this function here;
http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#INDEX
I think you will appreciate why it ought to be an 'array'.

I am still of the opinion this should be passed to the devs as a bug to allow them to determine just what the function should do.

(In reply to comment #6)
> I believe this should be an array formula.
> The first argument, 'reference' is always required.
> Only as 'array' is a result returned when this parameter is used on its own.
> When 'row' is then added both 'simple' and 'array' return results.
> Same when 'column' is added with 'row' so that both are present.
> Only as 'array' is a result returned with 'row' 0 or omitted and 'column'
> present.
> 

 Your own comments are reinforcing the need to improve the current help documents for the INDEX function in Calc.

> If you read the ODF spec. for this function here;
> http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.
> html#INDEX
> I think you will appreciate why it ought to be an 'array'.

Well, I still read the same as before. It says "ReferenceList|Array DataSource", meaning that the *DataSource* can be either a *reference* (simple formula) or an *array* (array formula).

So, again, INDEX can be used in a simple formula or in an array formula.

> 
> I am still of the opinion this should be passed to the devs as a bug to
> allow them to determine just what the function should do.

IMHO, the function itself is already defined and it seems to be working as defined, so I see this as a documentation problem in Calc.

Perhaps the Status of this bug report should be changed and some QA group should be cc'd.

According to comment 7 should be Component = Documentation?

So to sum-up...

(In reply to John Smith from comment #6)
> > > Perhaps this can be put forward as a bug to
> > > have the Array check-box automatically selected as it is for functions such
> > > as MMULT, GROWTH and TREND
> > ...
> > I have to disagree. INDEX is not always an array formula.
> > ...

So who decides?

> > IMHO, this is a documentation problem only, thus the doc's team should be
> > cc'd and the description of the bug should be changed accordingly.

> I am still of the opinion this should be passed to the devs as a bug to
> allow them to determine just what the function should do.

(In reply to Ady from comment #7)
> Perhaps the Status of this bug report should be changed and some QA group
> should be cc'd.

I can't speak ex cathedra for the QA Team, but I'm pretty sure we're okay letting others make the call here. I'll bring this up to the Devs *and* to the Design Team, and let them both weigh-in.

Regardless of how we decide to resolve the issue, it's valid, so Status -> NEW.

It's neither nor, you also don't need to enter it as array formula, but then it depends on the position context. If not an array formula, whether the result is #VALUE! depends on where (which cell) the formula is entered. What =INDEX(A1:C3,1) does is it returns the cell range A1:C1, if not in array context the implicit intersection of the formula position with the range is taken as usual, this is the same as in =A1:C1, so if the formula is anywhere in columns A:C then the value of row 1 in that column is the result, in any other column the  result is #VALUE! because there is no intersection with A1:C1.

Entered as array formula the result is an array with values of A1:C1, which can be entered on any cell position, e.g. the same as {=A1:C1}

All this is normal cell range evaluation behaviour regarding scalar/array context and not specific to the INDEX() function.

In the attached screenshot examples only results BLUE and RED work because only for those the formula expression can determine exactly one cell.

Created attachment 140986 [details]
strange index value error

I'm having trouble to make the returned arrays intersect with the current row. It works for non-empty cells, but fails on empty cells (red in the given document).

I thought it is due to the strange behavior of the function wizard sometimes giving the result as a preview or failing with a value error. So clicking different locations inside the function wizard it will suck up parameters or add spaces.

But trying to replicate this manually, I could not make it work. Only explicitly giving row and column solved it.

Could you please have a look at my instructions in the given document, if you can reproduce the strange screenshots also embedded in the document?

Thanx