sc/qa/unit/data/functions/spreadsheet/fods/hlookup.fods | 41 ++++++++--
sc/qa/unit/data/functions/spreadsheet/fods/vlookup.fods | 65 +++++++++++++++-
sc/source/core/tool/interpr1.cxx | 17 +++-
3 files changed, 112 insertions(+), 11 deletions(-)
New commits:
commit bf432958c1d6d204511a6bb32e2c06161d811676
Author: Winfried Donkers <winfrieddonk...@libreoffice.org>
AuthorDate: Sat Jan 14 18:01:12 2023 +0100
Commit: Eike Rathke <er...@redhat.com>
CommitDate: Sun Jan 15 16:02:49 2023 +0000
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 <er...@redhat.com>
Tested-by: Jenkins
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("a";{1|3|"b"|"d"};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("a",{1;3;"b";"d"},1,1)"
calcext:value-type="string">
+
<text:p>=HLOOKUP("a",{1;3;"b";"d"},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("a";{1|3|"b"|"d"};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("a",{1;3;"b";"d"},1,0)"
calcext:value-type="string">
+
<text:p>=HLOOKUP("a",{1;3;"b";"d"},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("a";{1|3|"b"|"d"};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("a",{1;3;"b";"d"},1,1)"
calcext:value-type="string">
+
<text:p>=VLOOKUP("a",{1;3;"b";"d"},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("a";{1|3|"b"|"d"};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("a",{1;3;"b";"d"},1,0)"
calcext:value-type="string">
+
<text:p>=VLOOKUP("a",{1;3;"b";"d"},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;"a";"d"|2;"b";"e"|3;"c";"f"};
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,"a","d";2,"b","e";3,"c","f"},
2, 1)" calcext:value-type="string">
+ <text:p>=VLOOKUP(3,
{1,"a","d";2,"b","e";3,"c","f"},
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();
