Created attachment 87072 [details]
Spreadsheet showing how to create match function problems

Follow the instructions in the spreadsheet to create the problems.  Don't save the spreadsheet because you may not be able to fix the errors which show up.

Attachment 87072 [details] clearly shows that the match function does not search the correct range when sheets are moved. The value returned in cells C2 of Sheet1, Sheet2 and Sheet3 should always be 1, 2 and 3, respectively, no matter how the sheets are -rearranged.

This problem still occurs on 4.1.14.  It seems Calc does not associate ranges - e.g. A2:A4 - with the correct sheet when sheets are re-ordered.

Hello,

I confirm with AOO 4.1.13, but sound like a dump of https://bz.apache.org/ooo/show_bug.cgi?id=127479

Ctrl + Shift + F9 give correct value after move sheet.

This problem report is a follow-on to 127479. It shows that the problem occurs without any use of the INDIRECT function.  If after moving sheet 3 between sheet 1 and sheet 2, you copy sheet2.b2 and paste it as unformatted text it into both sheet2.C2 and sheet2.C3 the results still do not match.  Other functions - e.g. COUNTIF - do not appear to have this problemso it may just be a problem with the match function not re-calculating properly.

Created attachment 87182 [details]
Functions INDEX, COUNTIF, etc. operate correctly but MATCH does not.

Maybe someone could compare the code for the MATCH function with the code for the INDEX function and figure out how to fix the MATCH function. This problem has been around for a long time now.

Set as confirmed by comment #4

code in question
main/sc/source/core/tool/interpr1.cxx

line:4349

if I check 4377
        else if (GetStackType() == svMatrix)
        {
            pMatSrc = PopMatrix();
            if (!pMatSrc)
            {
                PushIllegalParameter();
                return;
            }
        }

looking at Index (8347) in comparison
case svMatrix:
                {
                    if (nArea != 1)
                        SetError(errIllegalArgument);
                    sal_uInt16 nOldSp = sp;
                    ScMatrixRef pMat = GetMatrix();
                    if (pMat)
                    {
                        SCSIZE nC, nR;
                        pMat->GetDimensions(nC, nR);
                        // Access one element of a vector independent of col/row
                        // orientation?
                        bool bVector = ((nCol == 0 || nRow == 0) && (nC == 1 || nR == 1));
                        SCSIZE nElement = ::std::max( static_cast<SCSIZE>(nCol),
                                static_cast<SCSIZE>(nRow));
                        if (nC == 0 || nR == 0 ||
                                (!bVector && (static_cast<SCSIZE>(nCol) > nC ||
                                              static_cast<SCSIZE>(nRow) > nR)) ||
                                (bVector && nElement > nC * nR))
                            PushIllegalArgument();
                        else if (nCol == 0 && nRow == 0)
                            sp = nOldSp;
                        else if (bVector)
                        {
                            --nElement;
                            if (pMat->IsString( nElement))
                                PushString( pMat->GetString( nElement));
                            else
                                PushDouble( pMat->GetDouble( nElement));
                        }
                        else if (nCol == 0)
                        {
                            ScMatrixRef pResMat = GetNewMat(nC, 1);
                            if (pResMat)
                            {
                                SCSIZE nRowMinus1 = static_cast<SCSIZE>(nRow - 1);
                                for (SCSIZE i = 0; i < nC; i++)
                                    if (!pMat->IsString(i, nRowMinus1))
                                        pResMat->PutDouble(pMat->GetDouble(i,
                                                    nRowMinus1), i, 0);
                                    else
                                        pResMat->PutString(pMat->GetString(i,
                                                    nRowMinus1), i, 0);
                                PushMatrix(pResMat);
                            }
                            else
                                PushIllegalArgument();
                        }
                        else if (nRow == 0)
                        {
                            ScMatrixRef pResMat = GetNewMat(1, nR);
                            if (pResMat)
                            {
                                SCSIZE nColMinus1 = static_cast<SCSIZE>(nCol - 1);
                                for (SCSIZE i = 0; i < nR; i++)
                                    if (!pMat->IsString(nColMinus1, i))
                                        pResMat->PutDouble(pMat->GetDouble(nColMinus1,
                                                    i), i);
                                    else
                                        pResMat->PutString(pMat->GetString(nColMinus1,
                                                    i), i);
                                PushMatrix(pResMat);
                            }
                            else
                                PushIllegalArgument();
                        }
                        else
                        {
                            if (!pMat->IsString( static_cast<SCSIZE>(nCol-1),
                                        static_cast<SCSIZE>(nRow-1)))
                                PushDouble( pMat->GetDouble(
                                            static_cast<SCSIZE>(nCol-1),
                                            static_cast<SCSIZE>(nRow-1)));
                            else
                                PushString( pMat->GetString(
                                            static_cast<SCSIZE>(nCol-1),
                                            static_cast<SCSIZE>(nRow-1)));
                        }
                    }
                }
                break;

ohh my...

I check a fix... maybe this is easy to fix