| View previous topic :: View next topic |
| 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.htm
Last edited by Wheelz on Tue Sep 13, 2011 12:18 am; edited 1 time in total |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
philb1
OOo Enthusiast
Joined: 21 Mar 2011
Posts: 111
Location: Auckland
|
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
|
