Bug 74075 - MATCH behaves differently in Calc and Excel on search range of mixed types (texts and numbers)
Summary: MATCH behaves differently in Calc and Excel on search range of mixed types (t...
Status: RESOLVED INVALID
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.4.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium minor
Assignee: Tarun Kumar
URL:
Whiteboard:
Keywords: regression
Depends on:
Blocks:
 
Reported: 2014-01-26 10:26 UTC by Øyvind
Modified: 2014-03-18 04:38 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Test File (24.78 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-03 13:01 UTC, Øyvind
Details
Sample file comparing all options for third MATCH parameter (15.33 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-24 12:50 UTC, m.a.riosv
Details
Updated file with ascending and descending lookup_array (19.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-03-14 04:54 UTC, Tarun Kumar
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Øyvind 2014-01-26 10:26:11 UTC
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!
Comment 1 m.a.riosv 2014-01-26 15:38:13 UTC
Hi  Øyvind, thanks for reporting.

Please can you attach a sample file, to verify the issue.
(Remember to delete any personal information)
Comment 2 Øyvind 2014-02-03 13:01:44 UTC
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!
Comment 3 Øyvind 2014-02-24 09:33:51 UTC
No joy with this issue?
Comment 4 m.a.riosv 2014-02-24 12:50:21 UTC
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
Comment 5 m.a.riosv 2014-02-24 13:08:48 UTC
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
Comment 6 Øyvind 2014-02-26 08:35:43 UTC
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?
Comment 7 m.a.riosv 2014-02-26 09:24:35 UTC
There was an error with the MIME type, corrected now.
Comment 8 Kohei Yoshida 2014-03-13 17:03:05 UTC
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 .....
Comment 9 Kohei Yoshida 2014-03-13 17:35:22 UTC
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".
Comment 10 Kohei Yoshida 2014-03-13 17:38:18 UTC
I'll take this away from MAB.
Comment 11 Tarun Kumar 2014-03-14 04:54:13 UTC
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.
Comment 12 Tarun Kumar 2014-03-14 04:56:15 UTC
(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.
Comment 13 Tarun Kumar 2014-03-18 04:38:20 UTC
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.