Hi Winfried,
On Friday, 2022-12-16 16:05:19 +0100, Winfried Donkers wrote:
> 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.
> 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.
For cell ranges, value type mismatch between query value type (string) and cell value
type (numeric) of the found position is not a query match implementation
details changed quite a lot, but this is a general behaviour of range
queries, see
ScQueryCellIterator< accessType >::FindEqualOrSortedLastInRange()
https://opengrok.libreoffice.org/xref/core/sc/source/core/data/queryiter.cxx?r=05dba1a1#684
where after BinarySearch() maParam.mbRangeLookup=false is set before
GetThis() (the actual comparison is done in
ScQueryEvaluator::processEntry()
https://opengrok.libreoffice.org/xref/core/sc/source/core/data/queryevaluator.cxx?r=c3ea4a80#855
where isQueryByString() returns false and then mrParam.mbRangeLookup
also isn't set).
> So far, it proves difficult for me to implement XLOOKUP because of un
> expected behaviour in use cases.
I suggest to implement the type mismatch #N/A for both, cell range and
array/matrix, if that is what Excel does.
Merry solstice, happy holidays and a prosperous new year!
Eike
--
GPG key 0x6A6CD5B765632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A
Attachment:
signature.asc
Description: PGP signature
