Why does Calc's LOOKUP function give a wrong answer here?

[MrHuge]

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 driver’s name but the third score shows an incorrect driver name for the correct score, (gives the fourth driver’s 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?

[mariosv]

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.

[mrtweetyhack]

I created an account just to tell LibreOffice how stupid this is and how I will never use this shit product again

[mariosv]

Why do you blame your disabilities on other people?

[BeeRayDee]

mrtweetyhack is Correct !
This Is Absolutely An Idiotic Limitation - I Can Not Sort The Lookups, I ruins The Data Relationships!
It Is a Shitty Limitation.

[Pedro1]

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

[JakubNarębski]

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.

[mahfiaz]

As already mentioned but not exactly stated: You should either sort your data or use HLOOKUP or VLOOKUP with sorted parameter set to 0 (unsorted). Or if you like, you could use OFFSET and MATCH for the very same purpose (again, match with type 0).

So how do LOOKUP and friends work with (supposedly) sorted lists? It goes value by value until the next value is larger than the search criterion. It has one important benefit, your list might contain values 1, 2, 3, 4, 5 and when you search for 3.5 you get 3. With unsorted search it requires exact match and 3.5 would match nothing from this list.

So let’s say that your data is 1, 3, 5, 2, 6, 8. When you search for 2, you get 1. When you search for 7 you get 6 and when you search for 5.5 you get 2. Unsorted search is fun