sc/qa/unit/data/functions/spreadsheet/fods/hlookup.fods | 41 ++++++++-- sc/qa/unit/data/functions/spreadsheet/fods/match.fods | 46 ++++++++--- sc/qa/unit/data/functions/spreadsheet/fods/vlookup.fods | 65 +++++++++++++++- sc/source/core/tool/interpr1.cxx | 23 ++++- 4 files changed, 152 insertions(+), 23 deletions(-)
New commits: commit a5103adc413fb2cc8811c8691de5bfbe2df51961 Author: Winfried Donkers <[email protected]> AuthorDate: Sat Dec 31 15:54:57 2022 +0100 Commit: Xisco Fauli <[email protected]> CommitDate: Mon Jan 16 08:40:21 2023 +0000 tdf#152774 Fix incorrect result with MATCH, HLOOKUP and VLOOKUP. This is a combination of 2 commits. tdf#152774 Fix incorrect result with MATCH. Use case now complies with ODF 6.9.14 (MATCH). Added use case to unit test. xChange-Id: I749a979135fbc01a3bb1583092fb6d6a100e2d2d Reviewed-on: https://gerrit.libreoffice.org/c/core/+/144883 Tested-by: Jenkins Reviewed-by: Eike Rathke <[email protected]> (cherry picked from commit 0ce4c1e6898ba83d487f1b225dc9ee0bbc00d9fc) 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. xChange-Id: I19df718b6446098f614136f462662c753a515036 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/145514 Reviewed-by: Eike Rathke <[email protected]> Tested-by: Jenkins (cherry picked from commit bf432958c1d6d204511a6bb32e2c06161d811676) Change-Id: I749a979135fbc01a3bb1583092fb6d6a100e2d2d Reviewed-on: https://gerrit.libreoffice.org/c/core/+/145529 Reviewed-by: Eike Rathke <[email protected]> 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/match.fods b/sc/qa/unit/data/functions/spreadsheet/fods/match.fods index a050773c07e3..d5debd0d6339 100644 --- a/sc/qa/unit/data/functions/spreadsheet/fods/match.fods +++ b/sc/qa/unit/data/functions/spreadsheet/fods/match.fods @@ -5716,11 +5716,23 @@ <table:table-cell table:style-name="ce33"/> <table:table-cell table:number-columns-repeated="16"/> </table:table-row> - <table:table-row table:style-name="ro6"> - <table:table-cell table:style-name="ce13" table:number-columns-repeated="2"/> - <table:table-cell table:style-name="ce16"/> - <table:table-cell table:style-name="ce19"/> - <table:table-cell table:number-columns-repeated="3"/> + <table:table-row table:style-name="ro2"> + <table:table-cell table:style-name="ce13" table:formula="of:=MATCH("a";{1;3;"b";"d"};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:style-name="ce13" 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="ce48" table:formula="of:=ISERROR([.A118])" 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([.A118])" office:value-type="string" office:string-value="=MATCH("a",{1,3,"b","d"},1)" calcext:value-type="string"> + <text:p>=MATCH("a",{1,3,"b","d"},1)</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>Tdf#152774</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2"/> <table:table-cell office:value-type="float" office:value="4" calcext:value-type="float"> <text:p>4</text:p> </table:table-cell> @@ -5731,11 +5743,23 @@ <table:table-cell table:style-name="ce33"/> <table:table-cell table:number-columns-repeated="16"/> </table:table-row> - <table:table-row table:style-name="ro6"> - <table:table-cell table:style-name="ce13" table:number-columns-repeated="2"/> - <table:table-cell table:style-name="ce16"/> - <table:table-cell table:style-name="ce19"/> - <table:table-cell table:number-columns-repeated="3"/> + <table:table-row table:style-name="ro2"> + <table:table-cell table:style-name="ce13" table:formula="of:=MATCH(4;{"d";"b";3;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:style-name="ce13" 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="ce49" table:formula="of:=ISERROR([.A119])" 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([.A119])" office:value-type="string" office:string-value="=MATCH(4,{"d","b",3,1},-1)" calcext:value-type="string"> + <text:p>=MATCH(4,{"d","b",3,1},-1)</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>Tdf#152774</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2"/> <table:table-cell office:value-type="float" office:value="3" calcext:value-type="float"> <text:p>3</text:p> </table:table-cell> @@ -6255,4 +6279,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 0785318da871..d82acb37494c 100644 --- a/sc/source/core/tool/interpr1.cxx +++ b/sc/source/core/tool/interpr1.cxx @@ -5052,7 +5052,11 @@ void ScInterpreter::ScMatch() if (nHitIndex > 0) // valid hit must be 2nd item or higher { - PushDouble( nHitIndex); // non-exact match + if ( ! ( rItem.meType == ScQueryEntry::ByString && aMatAcc.IsValue( nHitIndex-1 ) ) && + ! ( rItem.meType == ScQueryEntry::ByValue && !aMatAcc.IsValue( nHitIndex-1 ) ) ) + PushDouble( nHitIndex); // non-exact match + else + PushNA(); return; } @@ -5060,6 +5064,7 @@ void ScInterpreter::ScMatch() return; } + // The source data is cell range. SCCOLROW nDelta = 0; if (nCol1 == nCol2) { // search row in column @@ -7504,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();
