I have a spreadsheet (made in Excel 2013, but I have made the sheet from scratch in LO just to try) and i seems there is a strange error when LO handles these functions. I have a list (Letters in alphabet) in one coloum and a number next to it (a = 1, b = 2 etc, c = 3..) And I am using this function to call the number, depending on what letters is inputted. =IF(D1="","",INDEX($DATA.$B$1:$B$39,MATCH($PASSWORD.B5,$DATA.$A$1:$A$39))) All letters UPTO "s" is fine, however all letters from this point gives an NA value for a reason I cannot explain. Me being silly or an actual bug? Thanks!
Hi Øyvind, thanks for reporting. Please can you attach a sample file, to verify the issue. (Remember to delete any personal information)
Created attachment 93281 [details] Test File As requested. File is created in Windows LO 4.2.0.4. Returns normal values from a though r, t though z returns NA and 0 though 9 is fine. Same problem with Ubuntu LO. Works fine in Excel 2013 I think it's a problem with my code though, cause this is just too strange to be a bug :) Thanks!
No joy with this issue?
Created attachment 94653 [details] Sample file comparing all options for third MATCH parameter Added the sample file with calculations for all types of third parameter. Reproducible: Win7x64Ultimate Version: 3.5.7.2 Build ID: 3215f89-f603614-ab984f2-7348103-1225a5b Version: 3.6.7.2 (ID de compilación: e183d5b) Version: 4.0.6.2 (Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24) Version: 4.1.6.0.0+ Build ID: 1c33633ef18274bf384c74c492195519be83c05 Version: 4.2.1.1 Build ID: d7dbbd7842e6a58b0f521599204e827654e1fb8b Version: 4.2.3.0.0+ Build ID: 5fd90cdd1fdb20ab7f6a2b67c384f0994f09a86b TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-02-21_22:38:15 Version: 4.3.0.0.alpha0+ Build ID: d84ccb39b744457cd47125beb4291c84223d5219 TinderBox: Win-x86@39, Branch:master, Time: 2014-02-22_10:05:06 Last working: Version: 3.3.4 OOO330m19 (Build:401) tag libreoffice-3.3.4.1
Wrong results for MATCH and VLOOKUP function. As in the sample both works with the last parameter to 0, but fails with it at 1 or without it. =MATCH(A1&"";$A$1:$A$36) fails from 's' =MATCH(A1&"^";$A$1:$A$36) fails from 'z' =MATCH(A1&".*";$A$1:$A$36) =MATCH(A1&"$";$A$1:$A$36) works fine
I can't check your file. Error: XML Parsing Error: not well-formed Location: https://bugs.freedesktop.org/attachment.cgi?id=94653 Line Number 1, Column 3: Forgive me for being thick, but this is an actual bug? Not me being useless in creating formulas?
There was an error with the MIME type, corrected now.
So, when the last parameter is 1 or omitted, the search range is supposed to be sorted in ascending order. Now, the search range in this test document contains both text and numeric values. Here is what ODF says about this. http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018430_715980110 "If the types are mixed, Numbers are sorted before Text, and Text before Logicals" In this test document, the numbers appear after the text values. Not sure whether the ODF is incorrect, Excel does sort texts and numbers differently, or .....
I have reservation for making this a MAB. This is very much a corner case where the right approach is not very clear. It's also possible that Excel's behavior is not intentional and simply a manifestation of how Excel implements its MATCH function. I for one don't know how to approach this "bug".
I'll take this away from MAB.
Created attachment 95772 [details] Updated file with ascending and descending lookup_array I think this function is working as expected (kindly correct me If I am wrong). Pasting a link as how it works in MS excel: http://office.microsoft.com/en-in/excel-help/match-function-HP010062414.aspx #1 For Match_type=0, exact match is found and hence it is working fine in all cases. #2 For Match_type = 1 and empty cases (default value is 1) prerequisite is lookup_array must be placed in ascending order. #3 For Match_type = -1 prerequisite is lookup_array must be placed in ascending order. I have attached same file with ascending and descending lookup_array and it seems to be working fine. If it is not the case, then let me know and I would like to take it for fixing.
(In reply to comment #11) > Created attachment 95772 [details] > Updated file with ascending and descending lookup_array > > I think this function is working as expected (kindly correct me If I am > wrong). > Pasting a link as how it works in MS excel: > http://office.microsoft.com/en-in/excel-help/match-function-HP010062414.aspx > > #1 For Match_type=0, exact match is found and hence it is working fine in > all cases. > > #2 For Match_type = 1 and empty cases (default value is 1) prerequisite is > lookup_array must be placed in ascending order. > > #3 For Match_type = -1 prerequisite is lookup_array must be placed in > ascending order. > > I have attached same file with ascending and descending lookup_array and it > seems to be working fine. > > If it is not the case, then let me know and I would like to take it for > fixing. Correction... #3 For Match_type = -1 prerequisite is lookup_array must be placed in descending order.
Hi Øyvind, Can you pls confirm? I am updating the status to "RESOLVED" as per my understanding. If this is not the case, will reopen it.