diff options
| -rw-r--r-- | sc/qa/unit/data/functions/spreadsheet/fods/hlookup.fods | 41 | ||||
| -rw-r--r-- | sc/qa/unit/data/functions/spreadsheet/fods/vlookup.fods | 65 | ||||
| -rw-r--r-- | sc/source/core/tool/interpr1.cxx | 17 |
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("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(); |