[opensuse] Has anyone tried using LOOKUP in an OO spreadsheet?
It seems straight forward (I'm not exactly a spreadsheet wizard), but I seem to be getting random/invalid lookups. Just wondering if anyone else had played with it? thanks Per -- Per Jessen, Zürich (21.1°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Per Jessen wrote:
It seems straight forward (I'm not exactly a spreadsheet wizard), but I seem to be getting random/invalid lookups. Just wondering if anyone else had played with it?
thanks Per
Could be an issue with formatting, ie the number you're looking up is actually stored as text rather than as a number. The lookup-type functions are particularly sensitive to that. If that's the problem, setting the right formatting (Format cells > Numbers) then using Text to Columns (under the Data menu) should reset the formatting and fix the problem. Alternatively, your data may just have spaces in spots you don't expect. If you want a second set of eyeballs, you can send me the file off list and I'll take a look. Nkoli -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Nkoli wrote:
Per Jessen wrote:
It seems straight forward (I'm not exactly a spreadsheet wizard), but I seem to be getting random/invalid lookups. Just wondering if anyone else had played with it?
thanks Per
Could be an issue with formatting, ie the number you're looking up is actually stored as text rather than as a number.
Hmm, interesting point - I'm actually looking up text, but the columns are almost certainly still just "General".
If you want a second set of eyeballs, you can send me the file off list and I'll take a look.
Thanks, I might just do that. A second set of eyes is always a Good Thing(R). /Per -- Per Jessen, Zürich (18.9°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Per Jessen wrote:
Nkoli wrote:
Per Jessen wrote:
It seems straight forward (I'm not exactly a spreadsheet wizard), but I seem to be getting random/invalid lookups. Just wondering if anyone else had played with it?
thanks Per
Could be an issue with formatting, ie the number you're looking up is actually stored as text rather than as a number.
Hmm, interesting point - I'm actually looking up text, but the columns are almost certainly still just "General".
If you want a second set of eyeballs, you can send me the file off list and I'll take a look.
Thanks, I might just do that. A second set of eyes is always a Good Thing(R).
/Per
If Find & Replace finds the value you're using as the search criterion, then make sure the search vector begins in the column that contains the match for the search criterion. The result vector can only be one column and it's probably better to have the search vector as one column as well since the match has to be in the first column. Also, if you're typing in the text rather than using a cell reference for the search criterion, double check that the text is enclosed in double quotes. That's all I can think of for now. Good luck. Nkoli -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Nkoli wrote:
If Find & Replace finds the value you're using as the search criterion, then make sure the search vector begins in the column that contains the match for the search criterion. The result vector can only be one column
It is.
and it's probably better to have the search vector as one column as well since the match has to be in the first column.
It is.
Also, if you're typing in the text rather than using a cell reference for the search criterion, double check that the text is enclosed in double quotes. That's all I can think of for now. Good luck.
Nope, the criterion is is a cell reference. My lookup (in Sheet2) =LOOKUP(A2;Sheet1.$A$2:$A$65536;Sheet1.$F$2:$F$65536) /Per -- Per Jessen, Zürich (18.4°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Per Jessen wrote:
My lookup (in Sheet2)
=LOOKUP(A2;Sheet1.$A$2:$A$65536;Sheet1.$F$2:$F$65536)
I sent Nkoli a copy of my shreadsheet off-line, and he pointed out the LOOKUP does not do an exact match, whereas VLOOKUP can be made to do.
From the OO Help page: "If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion."
Thanks Nkoli! /Per -- Per Jessen, Zürich (19.4°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Per Jessen wrote:
Per Jessen wrote:
My lookup (in Sheet2)
=LOOKUP(A2;Sheet1.$A$2:$A$65536;Sheet1.$F$2:$F$65536)
I sent Nkoli a copy of my shreadsheet off-line, and he pointed out the LOOKUP does not do an exact match, whereas VLOOKUP can be made to do.
she =P
From the OO Help page: "If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion."
Thanks Nkoli!
/Per
You're welcome! Nkoli -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Fri, 07 Aug 2009 10:47:14 +0200, Per Jessen wrote:
It seems straight forward (I'm not exactly a spreadsheet wizard), but I seem to be getting random/invalid lookups. Just wondering if anyone else had played with it?
I've used it once or twice, but I prefer VLOOKUP instead. I don't see anything in your lookup that looks invalid, just at first glance. Is this OO2 or OO3? Jim -- Jim Henderson Please keep on-topic replies on the list so everyone benefits -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Jim Henderson wrote:
On Fri, 07 Aug 2009 10:47:14 +0200, Per Jessen wrote:
It seems straight forward (I'm not exactly a spreadsheet wizard), but I seem to be getting random/invalid lookups. Just wondering if anyone else had played with it?
I've used it once or twice, but I prefer VLOOKUP instead.
I don't see anything in your lookup that looks invalid, just at first glance. Is this OO2 or OO3?
I'm still on 2.4 - I was going to try it out on OO3 on a different system too though. /Per -- Per Jessen, Zürich (19.3°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Per Jessen wrote:
Jim Henderson wrote:
I don't see anything in your lookup that looks invalid, just at first glance. Is this OO2 or OO3?
I'm still on 2.4 - I was going to try it out on OO3 on a different system too though.
Just tried it on OO3.1 - same result. Some of the results are correct, whether blank or not, but others appear to have been picked at random. /Per -- Per Jessen, Zürich (19.1°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Sun, 09 Aug 2009 09:57:52 +0200, Per Jessen wrote:
Per Jessen wrote:
Jim Henderson wrote:
I don't see anything in your lookup that looks invalid, just at first glance. Is this OO2 or OO3?
I'm still on 2.4 - I was going to try it out on OO3 on a different system too though.
Just tried it on OO3.1 - same result. Some of the results are correct, whether blank or not, but others appear to have been picked at random.
One thing I note from the documentation for LOOKUP is: "Additionally, the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results. If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion." Are you sure you're not running into this condition? This is one reason I prefer VLOOKUP. Unless you need th eresult to come from a different column and/or row. Jim -- Jim Henderson Please keep on-topic replies on the list so everyone benefits -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
participants (3)
-
Jim Henderson -
Nkoli -
Per Jessen