I have a sheet to record results from Grand Prix races, I use the LARGE function to sort out the points won, to list the top three point scorers. B24 =LARGE(C21:X21,1), B25=LARGE(C21:X21,2), B26 =LARGE(C21:X21,3). This shows the correct results. I then use the lookup function to show the name of the driver associated to these scores. C24 =LOOKUP(B24,C21:X21,C1:X1), C25 =LOOKUP(B25,C21:X21,C1:X1), C26 =LOOKUP(B26,C21:X21,C1:X1). The first and second scores show the correct drivers name but the third score shows an incorrect driver name for the correct score, (gives the fourth drivers name). If I contine to the fourth and fifth top scores it shows the correct driver name but the sixth shows the error #N/A. Any suggestions as to what I am doing wrong, or is this a bug?
Thanks MrHuge
I wonder why HLOOKUP and VLOOKUP have an option to use unsorted keys, while LOOKUP doesn't support it.
What is strange that whether the problem described happens or not might depend on if source is in different spreadsheet.
Form built-ín help:
LOOKUP Returns the contents of a cell either from a one-row or one-column range. Optionally, the assigned value (of the same index) is returned in a different column and row. As opposed to VLOOKUP and HLOOKUP, search and result vector may be at different positions; they do not have to be adjacent. Additionally, the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results.
If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion.
It is not a bug. The Lookup function only works if the primary key (the first column) is sorted in ascending order (sometimes it works, I have no idea why, in unsorted lists and that is what causes confusion)
If you sort your scores and drivers list in ascending order of the scores the Lookup function will work as expected
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-05-06 00:57:11 +0200
Seen: 427 times
Last updated: Nov 08 '12
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
