https://bugs.documentfoundation.org/show_bug.cgi?id=155551
--- Comment #4 from ady <[email protected]> --- (In reply to m.a.riosv from comment #2) > Looks that TRIM() doesn't shortcut for reference to the whole column A:A. > I think many text functions e.g., SUBSTITUTE has the same issue. Having TRIM() operating at least 123*128 = 15744 times is not the same as operating 123 times only. Here is what I mean: 1. In attachment 187596 from comment 1, worksheet "Pivot_stock_1", cell C2: =TRIM(A2) and copy down until C124. 2. In worksheet ledger, cell B2: =INDEX($Pivot_stock_1.B:B;MATCH(A2;$Pivot_stock_1.C:C;0)) 3. In worksheet ledger, cell D2: =INDEX(Pivot_stock_1.$B$2:$B$124;MATCH(A2;Pivot_stock_1.$C$2:$C$124;0)) 4. Copy B2:E2 down until B129:E129. With that, TRIM() will be operating 123 times (only), while the INDEX() and MATCH() functions will operate the same amount of times as before. In my system, this change generates a much faster response, which might suggest that the problem might not be "Open interval vs “fixed” interval for index/match or lookup function" (as the current title/subject suggests), but rather that the TRIM() function (operating on strings) is taking most of the resources. If that is correct (which needs to be corroborated by developers), then the subject of this report (at the time I am writing this) is not adequate/relevant. -- You are receiving this mail because: You are the assignee for the bug.
