Apache OpenOffice (AOO) Bugzilla – Issue 127479
MATCH function error when sheets are re-ordered and indirect addressing is used
Last modified: 2017-07-17 07:18:36 UTC
Created attachment 86180 [details] MATCH function error when sheets are re-ordered MATCH returns the wrong offset when used with INDIRECT and ADDRESS to specify the look-up array and the order of sheets is changed. In the attached spreadsheet, sheets are to be searched in the order they appear - I.E. initially A1:A10 on sheet "ROW5" should be searched, then, if sheet "ROW7" is moved between "MATCH" and "ROW5", A1:A10 on "ROW7" should be searched first. The function MATCH(SEARCH;INDIRECT(ADDRESS(1;1;4;1;SHEETNAME)&ADDRESS(10;1;4;1));0) does not work correctly: even though the cell containing the "SHEETNAME" is set to "ROW7" after the move, MATCH returns the offset to the SEARCH value on the "ROW5" sheet. There is a work-around which is to copy all the look-up arrays onto one sheet using INDIRECT(ADDRESS(row,column;4;1;sheet) functions and then use the MATCH function to search the copies. P.S. Don't use UNDO to reset the sheet order: CALC really messes thing up if you do.
The MATCH function works correct but it seems that there is a missing trigger for the INDIRECT function to recalculate the new string. (Maybe a bug or maybe there is a reason for it.) Pressing [Ctrl]+[Shift]+[F9] (hard recalculation of all) gives the right result.