Description: As part of the GSOD20 work to update the Calc Functions wiki pages, I have been reading the help page for the INDEX() function. Unfortunately, it appears to contain several errors and there are several glaring omissions. 1) There is no mention of using the INDEX function as an array function (either by ticking the Array button in the Function Wizard or by pressing Ctrl+Shift+Enter after typing the formula in the Input line). This is crucial to harness the full power of this function. 2) There is no mention of passing (multi-dimensional) inline array constants to the function, e.g., {=INDEX({1,3,5;7,9,10},{2;1},1)} is a valid array function call. 3) There is no mention of passing a multi-range area using the tilde (~) reference concatenation operator, e.g., =INDEX($B$1:$B$3~$F$1:$F$3,1,1). This could equally be written as =INDEX(($B$1:$B$3,$F$1:$F$3),1,1). 4) The introductory paragraph states that “Depending on context, INDEX returns a reference or content” is probably misleading – I have never seen the function return a “reference”. It can return the content of a single cell or, when entered as an array formula, can return the content of multiple cells. 5) The description of the Reference argument states that “If the reference consists of multiple ranges, you must enclose the reference or range name in parentheses”. This is not true, e.g., =INDEX(TwoAreas,1,1) is okay. 6) The description of the Row argument states that “In case of zero (no specific row) all referenced rows are returned”. This is only true if entered as an array function. Ditto for the corresponding statement for the Column argument. 7) The examples need to be reviewed and updated in the light of the above comments. This function is sufficiently complex and powerful that maybe linking to an ODT file containing a wide range of examples might be worth considering. Steps to Reproduce: Search for INDEX in the list of help topics. Actual Results: N/A Expected Results: N/A Reproducible: Always User Profile Reset: No Additional Info: Version: 7.0.3.1 (x64) Build ID: d7547858d014d4cf69878db179d326fc3483e082 CPU threads: 6; OS: Windows 10.0 Build 19041; UI render: Skia/Vulkan; VCL: win Locale: en-GB (en_GB); UI: en-GB Calc: threaded
Eike, could you comment this problem?
Olivier Hallot committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/35a7913f2075a9b73fd2c8d41473adface5711bb tdf#139027 Help information for Calc's INDEX() function
Just got pinged by this bug when modified (set to resolved) that apparently I missed earlier. For completeness of technical details: 4) INDEX() _does_ return a reference if the argument was one or more references, just the user doesn't see the reference when entering something like =INDEX(...) because of course the reference is resolved and the values displayed, like with any other reference (=A1). In =SUM(INDEX(...)) the SUM() function gets the reference passed that was returned by INDEX(). This may make a difference contrary to resolved values if the function handles a reference different from an array of values. 5) In =INDEX(TwoAreas,1,1) if TwoAreas is the name of a multiple range that of course is one parameter's argument, as is $B$1:$B$3~$F$1:$F$3 or ($B$1:$B$3,$F$1:$F$3) 6) Statement is wrong. "All rows" or "all columns" are also in non-array mode returned. =INDEX($A$1:$A$2;0;1) displays the value of A1 when entered in row 1, of A2 when entered in row 2, and #VALUE! when entered on any other row.
Olivier Hallot committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/cac49fecf0b35af451fd0e043b21f86a8a46b38d tdf#139027 More on INDEX() function
*** Bug 128800 has been marked as a duplicate of this bug. ***