Issue 33295 - error values in MATCH range match to 0
Summary: error values in MATCH range match to 0
Status: ACCEPTED
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOo 1.1.2
Hardware: All All
: P3 Trivial with 3 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: ms_interoperability
Depends on:
Blocks:
 
Reported: 2004-08-23 09:18 UTC by kotk
Modified: 2017-05-20 11:13 UTC (History)
1 user (show)

See Also:
Issue Type: ENHANCEMENT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description kotk 2004-08-23 09:18:06 UTC 
If the criteria is 0 then unsorted MATCH find #N/A, other error value or 0 
whatever is first.

Example:
a1  =na()
b1  =0
c1  =1

MATCH(0;a1:c1;0) results to 1

Does not apply to other values ... in same example MATCH(1;a1:c1;0) correctly 
results to 3 and MATCH(5;a1:c1;0) correctly results to #N/A

Same problems happen with unsorted ranges for HLOOKUP and VLOOKUP that contain 
error values. At least string values do not seemingly match with 0, but maybe 
they match with something else. ;)
Comment 1 frank 2004-08-25 11:16:40 UTC 
Hi Eike,

Excel sorts Errorvalues to the beginning of the sorted area just as we do. In
MATCH and the LOOKUP functions we set the value of the cell to evaluated by this
function to zero while Excel ignores them. We should work in the same way.

Frank
Comment 2 ooo 2004-08-25 15:09:23 UTC 
Slight correction: Excel sorts error results to the very end, not to the start.
Comment 3 kotk 2004-08-25 15:24:29 UTC 
Isnt it so that Excel sorts error values as extreme negative values not as 
zeroes?

Kotk
Comment 4 ooo 2004-08-25 15:42:54 UTC 
No, it sorts them even behind strings: -1,0,1,string,boolean,error
Comment 5 Marcus 2017-05-20 11:13:55 UTC 
Reset assigne to the default "issues@openoffice.apache.org".