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

Author Message
Wheelz
Power User

Joined: 14 May 2009
Posts: 93
Location: Wanganui, New Zealand

 Posted: Mon Sep 12, 2011 9:47 pm    Post subject: [SOLVED] VLOOKUP. What have I done wrong? Hi, I've created an abreviated list for sharing purposes and wonder if someone could please take a look at it and tell me what I've done wrong? I've used VLOOKUP in the same way as other sheets successfully but it doesn't seem to work on this fresh one. http://www.tsw.net.nz/files/Reverse_Lookup.ods Thanks, Pete_________________http://www.tsw.net.nz/ladder.htmLast edited by Wheelz on Tue Sep 13, 2011 12:18 am; edited 1 time in total
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Mon Sep 12, 2011 11:09 pm    Post subject: http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=43736&p=201590_________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
philb1
OOo Enthusiast

Joined: 21 Mar 2011
Posts: 111
Location: Auckland

 Posted: Mon Sep 12, 2011 11:25 pm    Post subject: You have to reference the left hand column, not the right hand column & the numbers have to be sorted into numeric order. http://www.4shared.com/file/MlDr6Okd/Reverse_Lookup.html
philb1
OOo Enthusiast

Joined: 21 Mar 2011
Posts: 111
Location: Auckland

 Posted: Mon Sep 12, 2011 11:38 pm    Post subject: It works if you sort the names into ascending order too
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Mon Sep 12, 2011 11:47 pm    Post subject:

 philb1 wrote: It works if you sort the names into ascending order too

1) You can not sort by the names AND by the numbers without tearing apart the relation between names and numbers.
2) VLOOKUP can not work from right to left
3) In ordered mode any spreadsheet lookup in default mode gives wrong positive matches for all search values being smaller than the search vector's first value.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
Wheelz
Power User

Joined: 14 May 2009
Posts: 93
Location: Wanganui, New Zealand

 Posted: Tue Sep 13, 2011 12:04 am    Post subject: Thanks very much to both of you. I can now see clearly the limitations, or should I say, restrictions when using this formula. Cheers, Pete_________________http://www.tsw.net.nz/ladder.htm
Wheelz
Power User

Joined: 14 May 2009
Posts: 93
Location: Wanganui, New Zealand

 Posted: Tue Sep 13, 2011 12:17 am    Post subject: I just tried it with an un-sorted list, ie. as entered at random, and it still returns the desired result. Am I just lucky this time?_________________http://www.tsw.net.nz/ladder.htm
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Tue Sep 13, 2011 12:28 am    Post subject: This problem occurs on a weekly basis in this forum and any Excel forum of the past 2 decades. In my first reply I linked to last week's short tutorial of mine which covers your problem exactly (right to left in unorderd mode). Searching any spreadsheet resource for keywords MATCH, LOOKUP, VLOOKUP pops up thousands of search results. It is not important if you sort the search vector or not. There are two very different modes of operation serving two very different requirements. One of the two modes (the default mode) requires that your search vector is sorted in ascending order. You want to use the other mode which needs to be activated explicitly._________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
 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