Issue 33295 - error values in MATCH range match to 0
Summary: error values in MATCH range match to 0
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:
Keywords: ms_interoperability
Depends on:
Reported: 2004-08-23 09:18 UTC by kotk
Modified: 2017-05-20 11:13 UTC (History)
1 user (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


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.

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.

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 

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