OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

They say the mind goes second ....

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Wed Jun 14, 2006 6:12 am    Post subject: They say the mind goes second .... Reply with quote

One of those weeks!

Cells I1 through J 12 are as below:
Jan 1
Feb 2
Mar 3
Apr 4
May 5
Jun 6
Jul 7
Aug 8
Sep 9
Oct 10
Nov 11
Dec 12

In D1, I have =VLOOKUP(C1;$I$1:$J$12;2)
and copy down column D for 12 cells. I put the months, as Jan, Feb, ... down column C.

Now, here are the results for the changing months in column C:

C D
Jan 1
Feb 2
Mar 3
Apr 4
May 5
Jun 6
Jul 2
Aug #N/A
Sep 9
Oct 8
Nov 8
Dec #N/A

So ...... What has happened after "Jun" ????

David.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jun 14, 2006 6:31 am    Post subject: Reply with quote

You left out the 4th param of VLookup, so you get unpredictable results from an unsorted list:
=VLOOKUP(C1;$I$1:$J$12;2;0)
works as espected
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Wed Jun 14, 2006 9:30 am    Post subject: Reply with quote

Villeroy wrote:
You left out the 4th param of VLookup, so you get unpredictable results from an unsorted list:
=VLOOKUP(C1;$I$1:$J$12;2;0)
works as espected


Thank you! I expected a default, with the following logic ..... Find whatever is to the right [second column] of this,found in the first column of this array.

Sorting should not be necessary, since it's simply first finding the item, then looking from left to right only. I think it's a limitation of programming here and in higher level language structures as well.

David.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jun 14, 2006 10:11 am    Post subject: Reply with quote

No need to sort if you set the last argument to 0 explicitly. The default works like this:
myRange:
0 low
1 medium
2 large
=VLOOKUP(0.9;myRange;2) >> low
=VLOOKUP(1.123;myRange;2) >> medium
=VLOOKUP(1000;myRange;2) >> large
=VLOOKUP(-1;myRange;2) >> #NA (smaller than first value)
IMHO the default, assuming 1 as last argument, should not be the default. But 0 as default would break old spreadsheet traditionals.
MATCH(search;vector;type) works the same way. Additionally MATCH accepts 0,1 and -1 as last argument. -1 indicates a vector sorted in descending order.
0 high
-1 medium
-2 low
=MATCH(-1.5;$A$1:$A$3;-1) >> 2
If you need VLOOKUP on a descending vector:
=INDEX($B$1:$B$3;MATCH(-1.5;$A$1:$A$3;-1)) >> medium
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Thu Jun 15, 2006 4:40 pm    Post subject: Reply with quote

Thanks, Villeroy. I may not be too old to learn, but I might also be getting too old to concentrate. Too many irons in the fire. Smile

David.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group