Created attachment 155815 [details] inconsistentcy in function wizard Single row/column range with the INDEX function behave inconsistent. When selecting a single row/column range the documentation states: "If reference is a one-dimensional column vector, column is optional or can be omitted. If reference is a one- dimensional row vector, row is optional, which effectively makes row act as the column offset into the vector, or can be omitted." But the real behaviour is that the optional column/row parameters 'have' to be omitted. When entering the function manually or in the function wizard and specify '1' as the row(/column) in a single row(/column) array the value gets used as the column(/row) index instead. (see screenshot) try: B1:B2 {=INDEX(A1:A2,1)} real behaviour: the target area gets filled with the content of first cell of the source array. B1=A1 B2=A1 expected behaviour (option1): the target area gets filled with the first row of the source array: B1=A1 B2=A2 expected behaviour (option2): the function wizard and the tooltip detect that the source range/array is a single column/row and reflect that in the interface somehow. (this may be hard if the source range has to be calculated)
an even more severe problem is that if you change the dimensions of the source range to/from a single column/row the INDEX function changes behaviour without notification. If source is a named ranged you are even less aware of the consequences of your change. aaand it's very incopatible to excels INDEX function: https://support.office.com/en-us/article/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
Created attachment 155825 [details] index example
Thank you for reporting the bug. I added an excel example spreadsheet, it seems excel calculates same way. Please attach a sample document, as this makes it easier for us to verify the bug. (Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.) I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
Created attachment 155828 [details] INDEX comparison of same formulas with different ranges. example file for clarification.
i would also like to emphasize, that these are probably two separate problems? one is the handling of the parameters and the other one is, that the function wizard / tooltip is inconsistent with the actual usage of parameters in the formula.
Created attachment 155829 [details] inconsistency in function wizard marked marked important stuff with red circles ;)
Created attachment 155861 [details] INDEX_test1_with_semicolon.xlsx (In reply to knittel from comment #6) > Created attachment 155829 [details] > inconsistency in function wizard marked > > marked important stuff with red circles ;) i checked with excel 2016: F9:H9 =INDEX(range2;1) -> {1,1,1} B9:D9 =INDEX(range1;1) -> REF! D17:D19 =INDEX(range1;1) -> REF! but i think i found the root case: excel needs a trailing semicolon: F9:H9 =INDEX(range2;1;) -> {1,2,3} B9:D9 =INDEX(range1;1;) -> {1,2,3} D17:D19 =INDEX(range1;1;) -> {1,1,1} and adding a ";" in LO makes F9:H9 work too: F9:H9 =INDEX(range2;1;) -> {1,2,3} @eike: you might be interested in this
The INDEX() function is always good for some confusion.. ;-) What happens here with {=INDEX(range2;1)} is that range2 is a one-dimensional row vector F4:H4, in which case if the Column argument is entirely omitted the Row argument acts as a column offset into the vector. The function then returns a reference to a single cell, here F4, which for the array result is repeated as often as necessary. Specifying the Column argument as empty like in {=INDEX(range2;1;)} lets the function return a row vector of the width of the given range, here the first of (the only) F4:H4. Same with Column 0 as in {=INDEX(range2;1;0)} That Excel does not handle {=INDEX(range1;1)} as the first row vector of B4:D6 to return B4:D4 and gives #REF! in that case seems to be their implementation detail. There is no actual reason not to do so. I agree that our help is lacking details, compare with https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#INDEX and also note the more detailed syntax there INDEX( ReferenceList|Array DataSource ; [ Integer Row ] [ ; [ Integer Column ] ] [ ; Integer AreaNumber = 1 ] ) regarding optional and omitted parameters. Forwarding to documentation.
*** This bug has been marked as a duplicate of bug 139027 ***