[Libreoffice-bugs] [Bug 124710] New: [1] IFS function does not use default result in certain cases

[bugzilla-daemon at bugs.documentfoundation.org]

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

            Bug ID: 124710
           Summary: [1] IFS function does not use default result in
                    certain cases
           Product: LibreOffice
           Version: 6.1.5.2 release
          Hardware: x86-64 (AMD64)
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: forouhcr at gmail.com

Description:
I have an IFS function with 4 conditions, the final condition being a default
result (i.e. if the previous 3 conditions are false, the final condition will
be true and give the result). But the final default condition does not "fire"
and the IFS returns #N/A.

Here's my formula:
=IFS(
     OR(I9="pago",I9="reci"),VLOOKUP(J9,$local_vlookup.A:C,2,0),
     AND(I9="tran",E9>0),"FT from",
     AND(I9="tran",E9<0),"FT to",
     1,"no idea"
    )
The result is #N/A.

I played with the formula by changing some conditions. When the VLOOKUP is
replaced with a text value, the IFS formula works (gives "no idea"). So the
problem is with VLOOKUP which returns #N/A (a valid value where there's no
match) which causes IFS to break down rather than treat the condition as FALSE.

Note that the IFS above came from an Excel 2016 where it works.

Steps to Reproduce:
1.Enter formula given in summary in cell K9
2.Create a sheet called local_vlookup, put any values in columns A, B and C
3.Make sure the VLOOKUP does not find a match (i.e. result is #N/A)

Actual Results:
#N/A

Expected Results:
String "no idea" - the default IFS result.


Reproducible: Always


User Profile Reset: No



Additional Info:
IFS should treat a VLOOKUP #N/A result as a FALSE (as Excel 2016 does) rather
than a formula error.

Please see attached ODS spreadsheet.

-- 
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/20190412/81938f91/attachment.html>