Version: 1.5.0 (using KDE 3.5.2 Level "a" , SUSE 10.0 UNSUPPORTED) Compiler: Target: i586-suse-linux OS: Linux (i686) release 2.6.13-15.8-default The lookup function always returns the last value in the results vector regardless of the search value. i.e. formula(s) like D1 - lookup(C1,$A$1:$A3;$B$1:$B$3) Results: COL/Row: a b c d Expected: D 1 11 300 12 222 75 2 12 75 cat 222 ERR 3 13 222 11 222 300
That comma in the example formula should be a semi-colon. NOTE: The LOOKUP syntax in pop-up guide under the insert menu shows "lookup(0,0,0)" The syntax example should show the correct delimiter. i.e. "Syntax: LOOKUP(0;0;0".
At least the syntax-example is fixed now (as in , got replaced with ; ). Thanks for that hint.
SVN commit 592093 by nikolaus: Functions Fix "LOOKUP function only returns last value". BUG: 127411 M +3 -3 branches/koffice/1.6/koffice/kspread/kspread_functions_reference.cc M +2 -2 trunk/koffice/kspread/functions/reference.cpp --- branches/koffice/1.6/koffice/kspread/kspread_functions_reference.cc #592092:592093 @@ -253,15 +253,15 @@ unsigned rows = lookup.rows(); if ((cols != rr.columns()) || (rows != rr.rows())) return Value::errorVALUE(); - Value res; - + Value res = Value::errorNA(); + // now traverse the array and perform comparison for (unsigned r = 0; r < rows; ++r) for (unsigned c = 0; c < cols; ++c) { // update the result, return if we cross the line Value le = lookup.element (c, r); - if (calc->lower (lookup, le) || calc->equal (lookup, le)) + if (calc->lower (le, num) || calc->equal (num, le)) res = rr.element (c, r); else return res; --- trunk/koffice/kspread/functions/reference.cpp #592092:592093 @@ -252,7 +252,7 @@ unsigned rows = lookup.rows(); if ((cols != rr.columns()) || (rows != rr.rows())) return Value::errorVALUE(); - Value res; + Value res = Value::errorNA(); // now traverse the array and perform comparison for (unsigned r = 0; r < rows; ++r) @@ -260,7 +260,7 @@ { // update the result, return if we cross the line Value le = lookup.element (c, r); - if (calc->lower (lookup, le) || calc->equal (lookup, le)) + if (calc->lower (le, num) || calc->equal (num, le)) res = rr.element (c, r); else return res;
The example is still misleading. I can't think of a case where the lookup and results vectors will change. Lookup implies some kind of a "fixed table". The example should reflect the fixed position of the table. i.e., LOOKUP(C1;$A$1:$A$3;$B$1:$B$3) Otherwise, I would be interested in seeing an example of a "floating lookup table".
This bug is still partially living in KSpread 1.6.3. If the first and second parameters are text in general formatted cells, the function still returns the last item in the result vector. An example follows: A B 2 Isogen 62.46 3 Megacyte 2,782.87 4 Mexallon 25.5 5 Morphite 9,111 6 Nocxium 111 7 Pyerite 4.44 8 Tritanium 3.9 9 Zydrine 2,297.97 ... 20 Isogen =LOOKUP(A20;A2:A9;B2:B9) [result 2,297.97] When I substitute numbers for the names, it works fine, but with text (either capitalized or all small) it returns the last item.
You need to log in before you can comment on or make changes to this bug.