Accepted.

*** Issue 90628 has been marked as a duplicate of this issue. ***

Comment from issue 90628:

ODFF defines the Searched and Results parameters as 'ForceArray'.

Calc doesn't handle for example
=LOOKUP(4; A1:C1*2; A3:C3)

entered in D4, where A1:C1*2 should be evaluated as an array expression, as does
Excel.


Additional note: the array case =LOOKUP(1;{1;2}) was implemented with CWS
odff03. Other cases remain to be done.

Won't make it for 3.1, targeting to 3.2

Created attachment 60894 [details]
patch 1.

The patch missed to change the parameter classification to ForceArray to make
the example from #desc4 work, also for Excel export. Comparing with Excel
another undocumented subtlety turned out: if the data <= query found is of type
string and the query was numeric, or vice versa, Excel returns #N/A, even if it
is said that a 0-9 < A-Z sorted data is searched. Changed function accordingly.

Other smaller changes:
eDataArray didn't need to be assigned in each case block, once before the switch
and switching on the value suffices.
The result of CollatorWrapper::compareString() is not one of COMPARE_EQUAL,
COMPARE_LESS, ... but values <0, ==0 or >0 instead.

I'll attach my current patch, a test case document and comment on remaining
problems.

Created attachment 61898 [details]
patch for modules sc and oox

Created attachment 61899 [details]
testcase Calc .ods

Created attachment 61900 [details]
the same testcase exported to Excel .xls

@lvyue: with the second patch applied, loading the .ods testcase displays
Err:504 in H2 and H3, these should also work.

@dr: with the second patch applied, testcase exported to .xls and loaded in
Excel it displays #VALUE! in cells E19:H28, see also attached .xls file.
Pressing F2 and enter on each then displays the expected value. The formulas are
not different from those in A19:D28, except of different cells referred. I don't
see a reason why this should be.. please investigate.

Thanks
  Eike

dr->er: Seems that there is another type of token class in Excel which is
neither exactly ref-type nor array-type. 

First, please do not change the first parameter, it must remain value-type.
For the second parameter, Excel seems to handle it as array-type in cell
formulas (value-type context) and as ref-type in other formula types, e.g.
defined names and array formula cells (ref-type and array-type contexts).

I have checked handling of ref-type parameters (e.g. SUM) and array-type
parameters (e.g. SUMPRODUCT) in all formula types. The export filter works
correctly for all types of formulas there. So I need to extend the formula
compiler for this new parameter type.

@dr:
> First, please do not change the first parameter, it must remain value-type.
Argh, sure, that was in error, bad me.. I'll attach a corrected patch.

> So I need to extend the formula compiler for this new parameter type.
Question remains whether there are other functions using this type yet
undiscovered. And if it has any influence on our internal
classification.

Created attachment 61912 [details]
patch with corrected Excel classification, based on current CWS odff06

Created attachment 61913 [details]
exported Excel .xls with the corrected parameter classification (no change in display behavior though)

Yes, what about the [HV]LOOKUP functions? I am currently thinking about a
generic testcase to be able to distinguish these cases.

I think all functions should be tested that contain an R parameter type in the
Excel filter function list, except functions that do not accept anything but
real references (e.g. the ROW() function).

The functions should be used in a cell formula receiving a value-type parameter
(e.g. "PI()"), and a (value-type*ref-type) parameter (e.g. "A1*PI()") as argument.
If Excel writes the function parameter as array-type, this function needs to be
changed to the new parameter type.

I will add the new functionality in the Excel filter to CWS odff06, and I will
check the functions.

@er: please remove the changed parameter type in the Excel filter from the
patch, to not run into confusion :-)

Created attachment 61926 [details]
Possible combinations of formula type, param type, token type

Created attachment 61933 [details]
patch without the Excel export bits against the now current CWS odff06

All 3 LOOKUP results in that i81214_paramtypes.xls are #N/A with the current patch.

Created attachment 61995 [details]
based on Eike's latest patch, changed for the third paramater, enable value, string or singleref.

The Excel export problem is now covered by issue 102702.

Another detail: a non-range lookup must return #N/A if the query was string and
the search vector is numbers or vice versa, an automatic conversion must not be
applied. For example, a query "3" must not match 3, a query 3 must not match "3".

In cws odff06:

revision 275000
sc/source/core/inc/parclass.hxx
sc/source/core/tool/interpr1.cxx
sc/source/core/tool/interpr4.cxx
sc/source/core/tool/parclass.cxx

Changed implementation of the patch to use PushCellResultToken() wherever
possible for inherited emptiness, that was broken. For this, introduced a new
parameter classification ReferenceOrForcedArray to be able to distinguish
between references passed that don't need to be unnecessarily converted to
arrays, and arguments that need to be converted to array in the parameter chain.

Submitted separate issue 104241 for the "string query should not return match
for last numeric value if no string <= query was found" problem, as it affects
LOOKUP, MATCH, VLOOKUP and HLOOKUP.

Reassigning to QA for verification.

Note: Exporting i81214_LOOKUP.ods to .xls and loading in Excel, the following
ranges differ and produce #N/A in Excel (instead of a value found in Calc)
because of issue 104241:

F13:I13
I13:I16
E37:E39

verified in internal build cws_odff06