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
