Description: If the value, that would match, is from a formula (for example, from a reference to another cell), match dos not return the row, but an #N/A Steps to Reproduce: Attached spreadsheet demonstrates the different results. Cell A15 contains the search value Column B contains the raw values, B15 contain =MATCH(A15;B2:B13;0) -> 4 Column C contains the values as ref to column B, C15 contain =MATCH(A15;C2:C13;0) -> #N/A If only the cell, that contains the value, that match would find, if it worked, is not a reference, than MATCH works again. This is demonstrated in column D Actual Results: MATCH fails, if found value is from a formula Expected Results: MATCH should find the value regardless, if it stems from a formula or not Reproducible: Always User Profile Reset: No Additional Info: Version: 7.2.0.4 / LibreOffice Community Build ID: 20(Build:4) CPU threads: 8; OS: Linux 5.13; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: de-DE 7.2.0-1 Calc: threaded
Created attachment 174586 [details] Document demonstrating the problem
No Repro in Version: 7.1.5.2 / LibreOffice Community Build ID: 85f04e9f809797b8199d13c421bd8a2b025d52b5 CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded Repro Version: 7.2.0.4 / LibreOffice Community Build ID: 9a9c6381e3f7a62afc1329bd359cc48accb6435b CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5 (cairo+xcb) Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded The problem seems to be related to the formatting of the cells in column C. Using a format showing the significant decimals results in MATCH working correctly/as expected (after recalculation). Procedure: - change format for data cells in column C to "#,##0.000 [$€-407];[RED]-#,##0.000 [$€-407]" (3 decimals, which equals significant digits of value in A15) - Recalculate using F9 or Data -> Calculate -> Recalculate (F9) => MATCH yields expected value 4
Additional info/observation: Using =MATCH(A15;VALUE(C2:C13);0)has the same effect as reformatting to number of significant digits. This looks like values of array C2:C13 are passed to function MATCH incorrectly (somehow rounded to visible digits?).
(In reply to Uwe Auer from comment #3) > Using =MATCH(A15;VALUE(C2:C13);0)has the same effect as reformatting to > number of significant digits. That works. Thanks for the workaround
(In reply to Andreas Blochberger from comment #4) > (In reply to Uwe Auer from comment #3) > > Using =MATCH(A15;VALUE(C2:C13);0)has the same effect as reformatting to > > number of significant digits. > That works. Thanks for the workaround My comment wasn't meant as a workaround, but as a hint to developers, where to start their search in code for the bug. Anyway: I'm glad that the note serves as workaround as well.
(In reply to Uwe Auer from comment #2) > No Repro in > > Version: 7.1.5.2 / LibreOffice Community > [...] > > Repro > > Version: 7.2.0.4 / LibreOffice Community > [...] > > The problem seems to be related to the formatting of the cells in column C. Maybe a duplicate of bug 143950?
(In reply to Ming Hua from comment #6) > > The problem seems to be related to the formatting of the cells in column C. > Maybe a duplicate of bug 143950? Probably yes.
Hi, I'm experiencing a bug that "feels" similar to this one. I'm happy to file another bug report if that's better. After the update to to LO 7.2.1 (from 7.1.x), MATCH formulas started to display N/A whereas the value definitely exists. I have a row with a list of value. I look for the minimum value in that row using the MIN function, and then wants to know in which column that value is found so I use the MATCH function over that same row, using the result of the MIN function as the Search Criteria. So this means the value has to exist (and it used to work fine before). I have been able to make the MATCH function work by displaying more decimals on the cell where the minimum is found (which value is used as the Search Criteria in MATCH) until there is no more rounding. So it seems MATCH (and maybe other formulas) does not if that *displayed* values are rounded, which is very odd! Screenshots attached.
Created attachment 175205 [details] MATCH broken 01
Created attachment 175206 [details] MATCH broken 02
Created attachment 175207 [details] MATCH broken 03
Created attachment 175208 [details] MATCH working 01
Created attachment 175209 [details] MATCH working 02
Forgot to put my system and software specifications: Version: 7.2.1.2 / LibreOffice Community Build ID: 20(Build:2) CPU threads: 8; OS: Linux 5.11; UI render: default; VCL: kf5 (cairo+xcb) Locale: en-GB (en_GB.UTF-8); UI: en-GB Ubuntu package version: 1:7.2.1~rc2-0ubuntu0.20.04.1~lo3 Calc: threaded Operating System: KDE neon 5.22 KDE Plasma Version: 5.22.5 KDE Frameworks Version: 5.86.0 Qt Version: 5.15.3 Kernel Version: 5.11.8-051108-generic (64-bit) Graphics Platform: X11 Processors: 8 × Intel® Core™ i5-8350U CPU @ 1.70GHz Memory: 15.4 GiB of RAM Graphics Processor: Mesa Intel® UHD Graphics 620
Apparently fixed for 7.2.2 with the commits for bug 144253.
And moreover bug 144740.