Ask Your Question

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

asked 2012-05-06 00:57:11 +0200

MrHuge gravatar image

updated 2014-07-06 15:13:38 +0200

bencomp gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-28 20:18:09.584556

4 Answers

Sort by » oldest newest most voted

answered 2012-05-06 01:49:07 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more


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

mrtweetyhack gravatar imagemrtweetyhack ( 2019-08-02 22:40:22 +0200 )edit

Why do you blame your disabilities on other people?

m.a.riosv gravatar imagem.a.riosv ( 2019-08-03 17:57:13 +0200 )edit

answered 2012-05-06 01:54:07 +0200

Pedro gravatar image

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

edit flag offensive delete link more

answered 2014-07-06 15:31:56 +0200

mahfiaz gravatar image

updated 2014-07-06 15:36:56 +0200

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 :)

edit flag offensive delete link more

answered 2012-11-08 15:49:33 +0200

Jakub Narębski gravatar image

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.

edit flag offensive delete link more

Question Tools


Asked: 2012-05-06 00:57:11 +0200

Seen: 7,695 times

Last updated: Jul 06 '14