[Libreoffice-bugs] [Bug 121488] ERROR.TYPE for "Search out of range" in Excel return 7 but in Calc #N/D

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=121488

Eike Rathke <erack at redhat.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Version|5.2 all versions            |Inherited From OOo

--- Comment #5 from Eike Rathke <erack at redhat.com> ---
Calc has more detailed errors than Excel. In this case the Err:502
(IllegalArgument) is because the field index to VLOOKUP() is 2 but there is
only one field. Calc returns #N/A only if the lookup-value is not found in an
otherwise correct context. Excel seems to return #N/A in any error case of
VLOOKUP().

Actually ERROR.TYPE() returning #N/A can happen with any of the more detailed
Calc error values because there is no defined mapping (and can't be), which
exists only for the seven error values Excel knows. Mapping Err:502 to #N/A
(=7) is not a solution because in other (or most) context it may be #VALUE!
(=3) instead, or something else depending on what Excel returns for some
function's error (which in most cases #VALUE! isn't helpful at all).

This isn't really solvable unless we'd transport additional information with
each error what it might be in Excel in the given context just in case there
would be an ERROR.TYPE() evaluating it. Which I think would be overdone and I'd
rather close this as wontfix.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20181119/83764cab/attachment.html>