behaviour of Calc function MATCH

[Winfried Donkers]

Hi Eike,

>> the functions MATCH and VLOOKUP.
>> =MATCH("b";{1,2,"d","e"};1) returns 2, which is in accordance with https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1018430_715980110
>> But =MATCH("b",B1:B4;1) with B1:B4 containing the values 1,2,"d","e" returns
>> #N/A, which IMHO is not in accordance with the ODF standard.
>> I know that Excel returns #N/A in both occasions.
> 
> #N/A is in accordance with ODF, 6.14.9 MATCH says for "MatchType = 1 or
> omitted" that "If Search is of type Text and the value found is of type
> Number, the #N/A Error is returned."; similar for "MatchType = -1" that
> "If Search is of type Number and the value found is of type Text, the
> #N/A Error is returned.". Same for HLOOKUP and VLOOKUP.

Yes, I now see the lines you mention. But following that are the lines 
"If MatchType is 1, the SearchRegion may be assumed to be sorted in 
ascending order, with smaller Numbers before larger ones, smaller Text 
values before larger ones (e.g., "A" before "B", and "B" before "BA"), 
and FALSE before TRUE. If the types are mixed, Numbers are sorted before 
Text, and Text before Logicals; evaluators without a separate Logical 
type may include a Logical as a Number. If MatchType is -1, then 
SearchRegion may be assumed to be sorted in descending order (the 
opposite of the above). ", which seem confusing when comparing wit the 
lines you mentioned.
Nevertheless, I will make that MATCH/HLOOKUP and VLOOKUP return #N/A 
error - after verifying how XLOOKUP and other functions using
ScQueryCellIterator< accessType >::FindEqualOrSortedLastInRange(..) or
ScQueryCellIterator< accessType >::FindEqualOrSortedLastInRange() ought 
to respond when Search is of type text and the value found is of type 
number.

> 
>> Why does Calc's MATCH conform partly with Excel behaviour and partly with
>> the ODF standard? Is this intended behaviour?
> 
> It looks more like implementation was omitted (or different in Excel
> back at that time?) from the array/matrix case. It would need to be
> implemented.
[...]
> I suggest to implement the type mismatch #N/A for both, cell range and
> array/matrix, if that is what Excel does.

Will do so, after creating a bug report about the inconsistency.

> Merry solstice, happy holidays and a prosperous new year!

Thanks, and like for you all!

Winfried