[Solved] LOOKUP merged cells problem

Discuss the spreadsheet application

[Solved] LOOKUP merged cells problem

Postby cje » Wed Jan 07, 2009 2:36 pm

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
Last edited by cje on Wed Jan 07, 2009 2:43 pm, edited 2 times in total.
OOo 3.2.X on Ms Windows XP
cje
 
Posts: 24
Joined: Mon Dec 15, 2008 12:37 pm

Re: LOOKUP merged cells problem

Postby Villeroy » Wed Jan 07, 2009 2:39 pm

Try =VLOOKUP($A1;Rawfile.$A$2:$P$382;4;0)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED]LOOKUP merged cells problem

Postby cje » Wed Jan 07, 2009 2:45 pm

wow, that was easy! :-)
thanks for quick reply, too!

may I ask you what makes the difference between 1 and 0?
OOo 3.2.X on Ms Windows XP
cje
 
Posts: 24
Joined: Mon Dec 15, 2008 12:37 pm

Re: [Solved] LOOKUP merged cells problem

Postby Villeroy » Wed Jan 07, 2009 3:37 pm

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   Expand viewCollapse view
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 
Attachments
MATCH_0_1.ods
MATCH, LOOKUP, VLOOKUP, HLOOKUP vertically and horizontally in ordered and unordered mode
(18.49 KiB) Downloaded 1213 times
Last edited by Villeroy on Wed Jan 28, 2009 4:29 pm, edited 7 times in total.
Reason: "Modernized" Attachment
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: floris v, MSN [Bot] and 35 guests