Description: =MATCH("a";{1;3;"b";"d"};1) returns 2 but should return #N/A. ODF, 6.14.9 MATCH says for "MatchType = 1 or omitted" that "If Search is of type Text and the value found is of type Number, the #N/A Error is returned." Steps to Reproduce: Enter =MATCH("a";{1;3;"b";"d"};1) in any cell in Calc. Actual Results: 2 Expected Results: #N/A Reproducible: Always User Profile Reset: No Additional Info: ODF, 6.14.9 MATCH says for "MatchType = 1 or omitted" that "If Search is of type Text and the value found is of type Number, the #N/A Error is returned."
Hi @Winfried, I think you forget to set up properly the last parameter, must be 0 to find the exact value. With 0 returns #N/A.
(In reply to m.a.riosv from comment #1) > Hi @Winfried, I think you forget to set up properly the last parameter, must > be 0 to find the exact value. With 0 returns #N/A. The last parameter is set properly (range sorted and ascending, first equal or less closest to search parameter matching) I am not looking for an exact value, but for the best match equal or less than the search value in a sorted (ascending) range. There is no exact match "a" in {1;3;"b";"d"} and according to the sorting conventions in ODF 3 is the best natch less than "a". However, because of the special case as mentioned in ODF 6.14.9 (search of type text and value found of type number) not 3, but #N/A is to be returned.
Created attachment 184418 [details] Sample test file (In reply to Winfried Donkers from comment #2) > .... > There is no exact match "a" in {1;3;"b";"d"} and according to the sorting > conventions in ODF 3 is the best natch less than "a". > However, because of the special case as mentioned in ODF 6.14.9 (search of > type text and value found of type number) not 3, but #N/A is to be returned. Sorry for my misleading. After a little test, seems it works as you have commented with ranges, but fails with an inner array.
Excel returns #N/A if you need to know.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/0ce4c1e6898ba83d487f1b225dc9ee0bbc00d9fc tdf#152774 Fix incorrect result with MATCH. It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
FWIW, doing: =MATCH("a";{1;3;"b";"d"};1) also results in "2" in portable Apache OpenOffice 4.1.13 on Windows 10. AOO4113m1(Build:9810) - Rev. 281f0d3533 2022-07-01 10:22 In LO 7.4.4.2, saving the file as xlsx and then opening it in excel (365), the same cell will result in "#N/A" (as already commented above).
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/bf432958c1d6d204511a6bb32e2c06161d811676 tdf#152774 Fix incorrect result with HLOOKUP and VLOOKUP. It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Winfried Donkers committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/a5103adc413fb2cc8811c8691de5bfbe2df51961 tdf#152774 Fix incorrect result with MATCH, HLOOKUP and VLOOKUP. It will be available in 7.5.1. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.