Issue 33295

Summary:	error values in MATCH range match to 0
Product:	Calc	Reporter:	kotk <dakotk>
Component:	ui	Assignee:	AOO issues mailing list <issues>
Status:	ACCEPTED ---	QA Contact:
Severity:	Trivial
Priority:	P3	CC:	issues
Version:	OOo 1.1.2	Keywords:	ms_interoperability
Target Milestone:	---
Hardware:	All
OS:	All
Issue Type:	ENHANCEMENT	Latest Confirmation in:	---
Developer Difficulty:	---

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".