https://bugs.documentfoundation.org/show_bug.cgi?id=154125
Bug ID: 154125
Summary: [ FILEOPEN ] [ FILESAVE ] Behavior of Calc function
INDEX() in array-like cases
Product: LibreOffice
Version: unspecified
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
CC: [email protected]
I have a case for the INDEX() function in Calc that behaves differently than in
Excel.
In Excel, the INDEX() function can process arrays natively, meaning that it can
be introduced with [ENTER] and it doesn’t need CSE.
Apparently, this is not completely true for the INDEX() function in Calc. The
specific result (either an array, or one simple value, or an error) may also be
dependent on the second and third arguments too.
For an example (file), see the following tutorial about the INDEX() function in
Excel, which includes a publicly available file to download, linked within the
text "sample workbook".
* The file can be downloaded from the tutorial page ("sample workbook").
* The full tutorial starts at:
https://www.ablebits.com/office-addins-blog/excel-index-match-multiple-criteria-formula-examples/
* The second section of that same page of the tutorial, which explains how
Excel handles the relevant "non-CSE" formula, starts at:
https://www.ablebits.com/office-addins-blog/excel-index-match-multiple-criteria-formula-examples/#non-array-multiple-criteria
* The incompatibility can be seen in the file named
"index-match-multiple-criteria.xlsx" > worksheet "Non-array formula" > cell G4.
* In Excel, that formula is introduced with a simple [ENTER] and returns a
valid result. In Calc, it seems as if CSE would be required (extra curly braces
shown in Calc), and it returns Err:504, which is carried from the inner INDEX()
function.
* The link to the file is:
https://cdn.ablebits.com/excel-tutorials-examples/index-match-multiple-criteria.xlsx
(Just for the record, I have no connection to the site nor the products
whatsoever.)
CC’ing Eike Rathke
--
You are receiving this mail because:
You are the assignee for the bug.
