tdf#152774 Fix incorrect result with HLOOKUP and VLOOKUP.

Use case now complies with ODF 6.9.5 (HLOOKUP) and 6.9.12 (VLOOKUP).
Added use case to unit test.

Change-Id: I19df718b6446098f614136f462662c753a515036
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/145514
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins

3 files changed, 112 insertions, 11 deletions

diff --git a/sc/qa/unit/data/functions/spreadsheet/fods/hlookup.fods b/sc/qa/unit/data/functions/spreadsheet/fods/hlookup.fods
index e3b38cf6c85b..8e14a315ac2a 100644
--- a/sc/qa/unit/data/functions/spreadsheet/fods/hlookup.fods
+++ b/sc/qa/unit/data/functions/spreadsheet/fods/hlookup.fods
@@ -1304,10 +1304,41 @@
      <table:table-cell table:style-name="ce15" table:number-columns-repeated="5"/>
      <table:table-cell table:number-columns-repeated="18"/>
     </table:table-row>
-    <table:table-row table:style-name="ro2" table:number-rows-repeated="2">
-     <table:table-cell table:number-columns-repeated="2"/>
-     <table:table-cell table:style-name="ce12"/>
-     <table:table-cell table:number-columns-repeated="29"/>
+    <table:table-row table:style-name="ro5">
+     <table:table-cell table:formula="of:=HLOOKUP(&quot;a&quot;;{1|3|&quot;b&quot;|&quot;d&quot;};1;1)" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>#N/A</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:#N/A" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>#N/A</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce25" table:formula="of:=ISERROR([.A39])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=FORMULA([.A39])" office:value-type="string" office:string-value="=HLOOKUP(&quot;a&quot;,{1;3;&quot;b&quot;;&quot;d&quot;},1,1)" calcext:value-type="string">
+      <text:p>=HLOOKUP(&quot;a&quot;,{1;3;&quot;b&quot;;&quot;d&quot;},1,1)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf152774</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="27"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro5">
+     <table:table-cell table:formula="of:=HLOOKUP(&quot;a&quot;;{1|3|&quot;b&quot;|&quot;d&quot;};1;0)" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>#N/A</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:#N/A" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>#N/A</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce25" table:formula="of:=ISERROR([.A40])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=FORMULA([.A40])" office:value-type="string" office:string-value="=HLOOKUP(&quot;a&quot;,{1;3;&quot;b&quot;;&quot;d&quot;},1,0)" calcext:value-type="string">
+      <text:p>=HLOOKUP(&quot;a&quot;,{1;3;&quot;b&quot;;&quot;d&quot;},1,0)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf152774</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="27"/>
     </table:table-row>
     <table:table-row table:style-name="ro2">
      <table:table-cell table:number-columns-repeated="2"/>
@@ -1391,4 +1422,4 @@
    <table:named-expressions/>
   </office:spreadsheet>
  </office:body>
-</office:document>
\ No newline at end of file
+</office:document>
diff --git a/sc/qa/unit/data/functions/spreadsheet/fods/vlookup.fods b/sc/qa/unit/data/functions/spreadsheet/fods/vlookup.fods
index 354aff0d763d..e0a0530a65e7 100644
--- a/sc/qa/unit/data/functions/spreadsheet/fods/vlookup.fods
+++ b/sc/qa/unit/data/functions/spreadsheet/fods/vlookup.fods
@@ -2559,8 +2559,67 @@
      </table:table-cell>
      <table:table-cell table:number-columns-repeated="36"/>
     </table:table-row>
-    <table:table-row table:style-name="ro2" table:number-rows-repeated="1048502">
-     <table:table-cell table:number-columns-repeated="34"/>
+    <table:table-row table:style-name="ro5">
+     <table:table-cell table:formula="of:=VLOOKUP(&quot;a&quot;;{1|3|&quot;b&quot;|&quot;d&quot;};1;1)" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>#N/A</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=#N/A" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>#N/A</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce27" table:formula="of:=ISERROR([.A74])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=FORMULA([.A74])" office:value-type="string" office:string-value="=VLOOKUP(&quot;a&quot;,{1;3;&quot;b&quot;;&quot;d&quot;},1,1)" calcext:value-type="string">
+      <text:p>=VLOOKUP(&quot;a&quot;,{1;3;&quot;b&quot;;&quot;d&quot;},1,1)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf152774</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="35"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro5">
+     <table:table-cell table:formula="of:=VLOOKUP(&quot;a&quot;;{1|3|&quot;b&quot;|&quot;d&quot;};1;0)" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>#N/A</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=#N/A" office:value-type="string" office:string-value="" calcext:value-type="error">
+      <text:p>#N/A</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce27" table:formula="of:=ISERROR([.A75])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=FORMULA([.A75])" office:value-type="string" office:string-value="=VLOOKUP(&quot;a&quot;,{1;3;&quot;b&quot;;&quot;d&quot;},1,0)" calcext:value-type="string">
+      <text:p>=VLOOKUP(&quot;a&quot;,{1;3;&quot;b&quot;;&quot;d&quot;},1,0)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf152774</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="35"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro9">
+     <table:table-cell table:style-name="ce21" table:formula="of:=VLOOKUP(3; {1;&quot;a&quot;;&quot;d&quot;|2;&quot;b&quot;;&quot;e&quot;|3;&quot;c&quot;;&quot;f&quot;}; 2; 1)" office:value-type="string" office:string-value="c" calcext:value-type="string">
+      <text:p>c</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>c</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce27" table:formula="of:=[.A76]=[.B76]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=FORMULA([.A76])" office:value-type="string" office:string-value="=VLOOKUP(3, {1,&quot;a&quot;,&quot;d&quot;;2,&quot;b&quot;,&quot;e&quot;;3,&quot;c&quot;,&quot;f&quot;}, 2, 1)" calcext:value-type="string">
+      <text:p>=VLOOKUP(3, {1,&quot;a&quot;,&quot;d&quot;;2,&quot;b&quot;,&quot;e&quot;;3,&quot;c&quot;,&quot;f&quot;}, 2, 1)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf152774</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="35"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro2" table:number-rows-repeated="24">
+     <table:table-cell table:number-columns-repeated="2"/>
+     <table:table-cell table:style-name="ce23"/>
+     <table:table-cell table:number-columns-repeated="37"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro2" table:number-rows-repeated="1048475">
+     <table:table-cell table:number-columns-repeated="40"/>
     </table:table-row>
     <table:table-row table:style-name="ro2">
      <table:table-cell table:number-columns-repeated="34"/>
@@ -2592,4 +2651,4 @@
    </table:named-expressions>
   </office:spreadsheet>
  </office:body>
-</office:document>
\ No newline at end of file
+</office:document>
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index c47aec4b052c..d82acb37494c 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -5064,6 +5064,7 @@ void ScInterpreter::ScMatch()
             return;
         }
 
+        // The source data is cell range.
         SCCOLROW nDelta = 0;
         if (nCol1 == nCol2)
         {                                           // search row in column
@@ -7508,16 +7509,26 @@ void ScInterpreter::CalculateLookup(bool bHLookup)
         {
             SCSIZE nX = static_cast<SCSIZE>(nSpIndex);
             SCSIZE nY = nDelta;
+            SCSIZE nXs = 0;
+            SCSIZE nYs = nY;
             if ( bHLookup )
             {
                 nX = nDelta;
                 nY = static_cast<SCSIZE>(nZIndex);
+                nXs = nX;
+                nYs = 0;
             }
             assert( nX < nC && nY < nR );
-            if ( pMat->IsStringOrEmpty( nX, nY) )
-                PushString(pMat->GetString( nX,nY).getString());
+            if (!(rItem.meType == ScQueryEntry::ByString && pMat->IsValue( nXs, nYs)))
+            {
+                if (pMat->IsStringOrEmpty( nX, nY))
+                    PushString(pMat->GetString( nX, nY).getString());
+                else
+                    PushDouble(pMat->GetDouble( nX, nY));
+            }
             else
-                PushDouble(pMat->GetDouble( nX,nY));
+                PushNA();
+            return;
         }
         else
             PushNA();