At 18:07 14/06/2007 +0700, Dodi Dewantara wrote:
Hello I'm using 2.2 OpenOffice.org. My problem is, I can't use VLOOKUP and
SUMIF function just like what it does in Microsoft Office. The example is
when I create a list of names (james,micky,jhon,willy,me) from A1:A5 and a
list of numbers (1,2,3,4,5) from B1:B5 and I try to look for james and I
create the search cell in C1 but I wrote just j . With this program
[=VLOOKUP(C1;A1:B5;2)] it shows me #N/A and when I wrote m it shows me 1.
Why when I not even wrote the full name I still have answer? and it's more
trouble when I use a lot of names.
I think it's just about time for me to tell you about this. Because it's
very important to me now. At least I hope you've read my message and could
answer the question or more even can give me an advice, but please be more
caution about vlookup and sumif.
And last of all I want to apologize if there's mistake in my message but
your considere is what I need the most
Thank you very much
The VLOOKUP function in Calc needs an extra parameter in the
situation you are describing. Without the fourth parameter present,
VLOOKUP assumes that the first column in the array specified in its
second parameter - here your list of names - is sorted in ascending
order. It also always returns a value, even if the search value is
not matched exactly, provided it is between the lowest and highest
value of the sorted list. You don't want that to happen here. So
you must indicate that your list of names is *not* sorted by adding
the boolean value FALSE as a fourth parameter to VLOOKUP:
=VLOOKUP(C1;A1:B5;2;FALSE)
You should find that this version works for you.
You haven't explained your difficulty with SUMIF. It may be that you
haven't followed Calc's "regular expressions" syntax accurately.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
