[Libreoffice] [Libreoffice-commits] .: sc/inc sc/source

[Kohei Yoshida]

Hi Eike,

On Mon, 2011-11-14 at 15:04 +0100, Eike Rathke wrote:

> IMHO (correct me if I'm wrong) for the sorted range lookup Excel returns
> #N/A if the search is of type Text and the last element found for
> less_or_equal is of type Number. 

Yup, you're right!  Tested with Excel 2007, and Excel returns #N/A even
when the last match found is of type Number.

> This is also what ODFF/OpenFormula
> defines, for example for VLOOKUP
> http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#__RefHeading__1018436_715980110

Ok.  I missed this sentence:

"If Lookup is of type Text and the value found is of type Number, the
#N/A Error is returned."

which supports the behavior before my revert (and current Excel's).

I was focusing this sentence:

"If the types are mixed, Numbers are sorted before Text, and Text before
Logicals..."

and interpreted it to mean that, if the lookup value is text, and the
match is not found within the text range, return the last matched value
from the number range.  But I guess my interpretation was not
correct. :-/

> My question was more in the direction if, when the mixed lookup is
> removed, we return the correct results for those spreadsheet functions
> accordind to ODFF. If yes, then the removal is fine.

The answer is yes, fortunately.  I'll revert my revert shortly.  Thanks
a lot for the follow-up explanation.  This makes things a lot easier for
me. :-)

Kohei

-- 
Kohei Yoshida, LibreOffice hacker, Calc