[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 Posted: Wed Jun 14, 2006 6:12 am    Post subject: They say the mind goes second .... 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Wed Jun 14, 2006 6:31 am    Post subject: 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
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Wed Jun 14, 2006 9:30 am    Post subject:

 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Wed Jun 14, 2006 10:11 am    Post subject: 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
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 Posted: Thu Jun 15, 2006 4:40 pm    Post subject: 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. David.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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