[Solved] LOOKUP merged cells problem

[cje]

Hi,
I'm having problem using the LOOKUP function in merged cells.

This is my problem:
A5 = Merged cell B5 and C5 (in my sheet A5 is "10 11 - 'some text'")
D5 = VLOOKUP($A5;Rawfile.$A$2:$P$382;4)

This return a value from another cell. I suppose this is because the function is reading the content of A1 wrong.
I've tried to "trim" the cell content but it doesn't remove all spaces - so the problem doesn't disappear
I've also tried to add a "_" instead of the space but it doesn't change anything either.

So, how can I make the LOOKUP function read the cell correctly?

Thanks for any suggestions

[Villeroy]

Try =VLOOKUP($A1;Rawfile.$A$2:$P$382;4;0)

[cje]

wow, that was easy!
thanks for quick reply, too!

may I ask you what makes the difference between 1 and 0?

[Villeroy]

All spreadsheets I'm aware of use lookup functions to get things like grades from points or values from thresholds. The functions in question are MATCH, which returns a position number and [V/H]LOOKUP which return another cell's looked up value.

Code: Select all

0  Low
1  Mid
2  High

=VLOOKUP(1.5 ; range ; 2) => "Mid". The function matches at value 1, which is the last value <= 1.5
=VLOOKUP(1.5 ; range ; 2 ; 1) is exactly the same. The last argument defaults to 1 if missing
=VLOOKUP(999 ; range ; 2) => "High" since 2 is the last value <= 999
=VLOOKUP(-1 ; range ; 2) => #NA since there is no value <= -1

This so called "ordered mode" requires the search vector to be sorted in ascending order. A vector is a single column or row of cells, VLOOKUP always uses the first column of the given range as search vector.

The "unordered mode" has to be enforced with an explicit zero as last argument. This mode is by far slower than ordered mode, but it does not require the search vector to be sorted. It returns #NA unless there is an exactly matching value in the search vector.
Today this unordered mode seems to be the most frequently used one, since most people use spreadsheets as a (bad and error-prone) database replacement. But for compatibility reasons the ordered mode still remains the default mode.

For the records:
Function LOOKUP does not have a switch for unordered mode. LOOKUP always assumes ordered mode when it performs vertical or horizontal lookups from arbitrary (disjunct) vectors.

When matching strings, you need to take care of possibly unwanted pattern matching (regular expressions), whole-cell-matching and case-sensitivity, all of which can be set for the current document under menu:Tools>Options...Calc>Calculation.
This makes the whole affair even more prone to errors, so always prefer lookups of unambiguous numbers.

Matching numbers may fail due to the well known rounding issues in spreadsheets. Displayed figure 3.1416 may or may not not be the exact value 3.1416.

A combination of functions INDEX and MATCH is more flexible. It can be used for everything the other functions can do plus several additional lookups, since MATCH knows a third mode for descending ordered lists and you can implement an unordered version of LOOKUP. For instance:
INDEX(Vector2 ; MATCH(Value ; Vector2 ; 0))
is like a vertical LOOKUP(Value ; Vector1 ; Vector2) in unordered mode.

Edit: 2009-01-10: Attached a simple example with MATCH in ordered and unordered mode

Edit: 2009-01-28: Improved attachment