Match cannot find number in formula cell but okay in value cell

[DanielWong]

Attached is a screen shot of results.

The issue comes with the 2nd and 3rd columns (both starting with 0.911030427)
The 2nd column have formula in their cells.
The 3rd column below the row with 3756 in it, is a cut and paste of the 2nd column text, numbers etc… only, no formula.

0.911030427 in both cases are calculated from =MAX(AK11:AK14717) and 1 column removed.
The 14707 numbers are calculated by using =MATCH(AK2,AK11:AK14717) and the row with image-??? in them is =INDIRECT(“A”&(AK3+10)). The tables themselves are a mixture of formulas and spaces e.g. =IF(ISBLANK($J11),"", AK11) etc…

As you can see, the maximum calculated is correct but the 2nd column cannot find where the 0.911030427 is and returns the last line of the table.

Another spreadsheet like this does not show any similar problems. Just this 2nd spreadsheet in the file.

Is this a known bug?

(I am using Version:5.1.6.2 Build ID: 1:5.1.6-rc2-0ubuntu1-xenial3)

[mikekaganski]

First, the explanation here is complex, and it’s worth providing a sample document instead of screenshot. Btw, the screenshot could also contain col/row names, if you arranged the view accordingly - that would allow to refer co cells by names, instead of “The 3rd column below the row with 3756 in it”.

Wrt “Is this a known bug” - when asking such questions, first please test with a ~current version of LO (5.1 is EOLed long ago).

[mariosv]

Take a look to MATCH help, you are not using the third parameter, and maybe is needed in this case.

[DanielWong]

Thanks for answering.
I got the LibreOffice with the Ubuntu distro and I would upload the file if it did not exceed the upload limits. hence my screenshot of the problem. The file is 8.3M and the max upload shows a limit of 1.4M. I suspect that if I deleted a spreadsheet (2 in the file and only 1 show this problem), the problem would go away.

[DanielWong]

Set the 3rd parameter to 0 and it worked! Thanks. The strange thing is that I did the same for the previous spreadsheet in the file and there were no issues.