Apache OpenOffice (AOO) Bugzilla – Issue 78781
ODFF: Excel INDEX() compatibility
Last modified: 2013-08-07 15:15:24 UTC
When using the INDEX(reference, row, column), Excel handles parameter differently based on if a 3rd parameter was passed. ex: input: =INDEX($D$27:$P$27;2) ms result: (value of E27) oo result: err:504 i did some testing on this and it appears that when no 3rd param is passed that excel uses the second as the column and assumes 1 for the row, as calc requires that the a row is atleast a blank value when a column is used.
I agree, Excel handles the parameters a bit differently, specially with references with a size of only one row. Confirming this issue, keyword: MS-Interoperability
Hi, regarding your example, Excel works faulty. You have just one row and want a result from the second row. Giving here other results than an Error is IMHO a fault. Calc on the other hand gives you a hint that something in the parameterlist of the function is faulty. This is how it should be. I think we should not copy the errors Excel make. Frank @Eike It seems that Excel treats such one row constructs in a special way. Should we double this ? If so, please set the target accordingly otherwise close as invalid.
It's surely strange behavior, but it's not an error of Excel, that's the way how they defined INDEX to work in what they call "array form" of the function with no 4th parameter being present. With a one-dimensional array the row respectively column parameter is optional, and whatever parameter is present is taken for the offset.
ODFF relevant.
Created attachment 52182 [details] patch1
In cws odff03: sc/source/core/tool/interpr1.cxx 1.56.20.4
Reassigning to QA for verification.
*** Issue 89550 has been marked as a duplicate of this issue. ***
verified in internal build cws_odff3
Verified with DEV300-m28 Linux X64
Verified in DEV300m28 and Linux - Closing - Sophie