Search code examples
excelexcel-2007

Has anybody encountered strange behavior of LOOKUP()?

NOTE: I don't need a solution for the actual problem to be solved with Excel. I want to understand and discuss the behaviour. And I want to see, if other people can reproduce the situation and make them aware of it.

  • Situation is extremely simplified for debugging the problem.
  • I use Excel 2007 in an cooperate environment

In a calculation made on a two-week basis, I have to fill in numbers for the two-week interval that are valid a whole month. For example first two weeks of December I have to use value 12, second two weeks again, than first to weeks of January I have to use 1, second two weeks again.

enter image description here

Therefore I have a column for each two-week period with a field of current month (green area in screenshot). The values valid for the month are in a second table (red area). To retrieve the value for current period, I use a LOOKUP()call:

(A2): =LOOKUP(A1;$A$9:$H$9;$A$10:$H$10)
...
(P2): =LOOKUP(P1;$A$9:$H$9;$A$10:$H$10)

As you can see in the screenshot, the function goes havoc and retrieves false values.

For testing, I reproduced the situation with the international phonetic alphabet instead of month names. Therefore:

(A5): =LOOKUP(A4;$A$12:$H$12;$A$13:$H$13)
...

This time, the function works well. Therefore I guess, it has something to do with the name of months. Maybe they have some internal representation, despite the fact, the cell are formatted as "text".

I already tested vertical vectors instead of horizontals in the red area. They lead to the same situation.

NOTE:

I finally solved the original problem by using HLOOKUP() and VLOOKUP(). There I found another clue. Both show the same behaviour if [not_exact_match] is committed or set to true but work fine, if exact_match is enforced. So, how can Februar be an approximate match to Dezember?

Solution

  • See http://office.microsoft.com/en-gb/excel-help/lookup-function-HP010342671.aspx

    For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order.

    This is not the case with the texts "Dezember", "Januar", "Februar", ...

    If you would use real dates 01.12.2013, 01.01.2014, 01.02.2014, ... and format them as "MMMM", then it should work. Of course the lookup_value also has to be in that form.

    Same problem with VLOOKUP and not exact match. http://office.microsoft.com/en-gb/excel-help/vlookup-function-HP010343011.aspx

    If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.