View previous topic :: View next topic 
Author 
Message 
deltareum General User
Joined: 21 Apr 2007 Posts: 10

Posted: Mon May 28, 2007 10:22 am Post subject: Trouble with the Lookup function 


I use OO Calc 2.0.
I am using the lookup() function to search a column in another sheet for a value and place the corresponding result from another column of that sheet. My function looks like this:
Code:  LOOKUP(C2;Sheet1_2.D$1:D$3000;Sheet1_2.B$1:B$3000)

(BTW, how do I address an entire column (like MS excel does),rather than the ugly code I have above?)
The trouble is, it always comes back with #N/A error. Yet, when I manually search for the value of C2 in the D column of Sheet1_2, I always find it.
Any ideas as to what might be wrong????
Is this function fully implemented in 2.0 and bugfree?
Last edited by deltareum on Tue May 29, 2007 10:06 am; edited 1 time in total 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany


Back to top 


deltareum General User
Joined: 21 Apr 2007 Posts: 10

Posted: Mon May 28, 2007 11:06 pm Post subject: 


When I try to add a fourth argument, 0, it does not work. It gives me Err:504. 

Back to top 


noranthon Super User
Joined: 07 Jul 2005 Posts: 3318

Posted: Mon May 28, 2007 11:14 pm Post subject: 


Have you tried using VLOOKUP instead of LOOKUP? _________________ search forum by month 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Tue May 29, 2007 9:59 am Post subject: MATCH, LOOKUP, VLOOKUP, HLOOKUP 


deltareum wrote:  When I try to add a fourth argument, 0, it does not work. It gives me Err:504. 
Help on Error codes in Calc wrote: 
504 Parameter list error
Function parameter is not valid, for example, text instead of a number, or a domain reference instead of cell reference.

In this case we pass too many. LOOKUP accepts 2 or 3 arguments.
:oops: I apologize for spreading confusion. You asked for LOOKUP rather than VLOOKUP. Let's start a tutorial.
1. How do these functions work by default
2. How can we modify the operation mode
3. How to overcome all restrictions by combining two functions
4. How to get even more
5. About #NA and other errors
6. Related settings in the options panel Menu:Tools>Options>Calc>Calculation
7. How to check for sorting?
1. How do these functions work by default
Functions MATCH, LOOKUP, VLOOKUP, HLOOKUP have been around since many years in all common spreadsheets. They are somewhat redundant and their logic is not easy to understand. But any spreadsheet program has to implement them according to "tradition" in order to keep compatibilty.
The most simple one is MATCH. It takes one value and one vector (array of values in a column or row) and returns the position where the value has been matched within the vector. Let's assume a simple vector of 3 values in A1:A3.
10
20
30
=MATCH( 0; $A$1:$A$3) returns #NA (value not available)
=MATCH(10; $A$1:$A$3) returns 1 (match at first position)
=MATCH(20; $A$1:$A$3) returns 2
=MATCH(25 ;$A$1:$A$3) returns 2 too. Why?
Each one of the 4 functions assumes a sorted vector and compares each value in the vector with the search value until the vector's value exceeds the search value.
In the last example (match 25) it goes like this:
@Position 1: 10 <= 25 ? Yes
@Position 2: 20 <= 25 ? Yes
@Position 3 :30 <= 25 ? No > return position #2 (last Yes)
The first formula (match 0) returns #NA since the first comparison already fails. Any search value >30 will match at last position.
Obviously you get no reliable results if the vector is unsorted:
When the comparison matches at some position in an unsorted vector there may be better matches (smaller values) below, that are not taken into consideration.
This is the default behaviour of all functions MATCH, LOOKUP, VLOOKUP, HLOOKUP.
Both LOOKUP and MATCH work vertically as well as horizontally (depending on the orientation of the vector) whereas HLOOKUP is the horizontal cousin of VLOOKUP.
Let's stick with vertical matches in columns:
LOOKUP(value ; search_vector ; return_vector) can match in any column and return the corresponding value out of any other column. If last argument return_vector is missing the result is a value picked from the search vector.
VLOOKUP(value ; search_range ; N ; mode) always matches a value in the first column of search_range and returns a value from the Nth column of search_range. N must not exceed the number of columns in search_range. When you leave out the mode argument or set it to 1, you get the default behaviour.
Twocolumn example, demonstrating all functions except HLOOKUP (I stick with the vertical stuff HLOOKUP is exactly like VLOOKUP if you tilt your mind by 90°):
10 Ab
20 Bc
30 Cd
(I put those 6 values into F1:G3).
Match string instead of numbers in default mode:
=MATCH("Aa";$G$1:$G$3) > #NA ("Aa" is smaller than the first)
=MATCH("Bc";$G$1:$G$3) > 2 (exact match at position 2)
=MATCH("Cc";$G$1:$G$3) > 2 ("Cc" is alphabetcally between "Bc" and "Cd")
You see the analogy to numeric matches in default mode.
Now lookup an alpahbetic string by given numeric value:
=LOOKUP(0; $F$1:$F$3 ; $G$1:$G$3) > #NA
=LOOKUP(20; $F$1:$F$3 ; $G$1:$G$3) > "Bc" (exact match in F returns value from G)
=LOOKUP(25; $F$1:$F$3 ; $G$1:$G$3) > "Bc" (last match <= 25 in F returns value from G)
VLOOKUP can be used in the same manner:
=VLOOKUP(25; $F$1:$G$3; 2) > "Bc" (last match <= 25 in F returns value from 2nd column in F1:G3)
The following can not be done with VLOOKUP:
=LOOKUP("Bc; $G$1:$G$3 ; $F$1:$F$3) > 20 (last match <= "Bc" in G returns value from preceeding column F). VLOOKUP always matches topdown in the first column and returns a value from the given offset at the right).
2. How can we modify the operation mode
This default behaviour can be modified by appending an optional argument zero. Appending a 1 means the same as the default.
Both formulae
=MATCH(25 ;$F$1:$F$3; 0)
=VLOOKUP(25 ;$F$1:$G$3 ; 2 ; 0)
return #NA since the exact value 25 is not there. The vector is scaned until there is an exact match with the search value and the position of this first match is returned as result of the function.
MATCH returns the matching position as number, whereas the others return an actual value out of the search range. The way how they match values at some position is exactly the same.
If the search vector is not sorted ascending and/or we need to get the first exactmatch then we need to append a modeargument of 0.
BUT: LOOKUP is the only member of the gang of four where you can not turn off the default mode.
3. How to overcome all restrictions by combining two functions
Until now we can not lookup values from a preceeding column that matches exactly with a search value in another column behind. LOOKUP can match in default mode only.
This is not a big problem if we know that we can get all the functionality of LOOKUP, VLOOKUP and HLOOKUP from a combination of MATCH and INDEX.
INDEX(range; nRow ; nColumn) returns a value from range at the position of nRow and nColumn.
Let's get an unsorted range J1:K3 and try exact matching.
30 B
10 C
20 A
=INDEX($J$1:$K$3 ; 1 ; 1) > 30 (1st row, 1st column)
=INDEX($J$1:$K$3 ; 3 ; 2) > "A" (3rd row, 2nd column)
Good to know: We can omit the column argument if the range is a single column (a vector).
=INDEX($J$1:$J$3 ; 1) > 30 (1st row in J)
=INDEX($K$1:$K$3 ; 3) > "A" (3rd row in K)
Now match 10 exactly in first column and get the value in 2nd column of J1:K3:
=INDEX($J$1:$K$3 ; MATCH(10;$J$1:$J$3;0) ; 2) > "C"
This is equivalent to slightly simlpier VLOOKUP(10 ; $J$1:$K$3; 2; 0
Now match "C" exactly in 2nd column and get the value in 1st column:
=INDEX($J$1:$K$3 ; MATCH("C";$K$1:$K$3;0) ; 1) >10
or with singlecolumn vector, omitting the column index:
=INDEX($J$1:$J$3 ; MATCH("C";$K$1:$K$3;0) ) > 10
There is no equivalent VLOOKUP since we don't match in first column, and LOOKUP can't do that since it can't be modified to do exact matching.
The equivalent to the above example
=LOOKUP("Bc; $G$1:$G$3 ; $F$1:$F$3) > 20 (last match <= "Bc" in G returns value from preceeding column F). VLOOKUP always matches topdown in the first row and returns a value from the given offset at the right).
where we used LOOKUP (sorted default mode only) is:
=INDEX($F$1:$G$3 ; MATCH("C";$G$1:$G$3) ; 1)
or
=INDEX($F$1:$G$3 ; MATCH("C";$G$1:$G$3 ; 1) ; 1)
Again, we match in default sorted mode if the last argument is missing or if it is 1.
Now we can match values forward and backward in rows and in columns in sorted mode or exact mode. INDEX with MATCH provides a solution for all cases where we could use (V/H)LOOKUP.
This combination can do even more.
Help on MATCH function wrote: 
If Type = 1 or the third parameter is missing, the last value that is smaller than or equal to the search criterion is returned. This applies even when the search array is not sorted. For Type = 1, the first value that is larger than or equal is returned.

"Type" is what I used the term "mode" for.
That means that MATCH knows 3 modes: 1 (or missing) for default sorted mode, 0 for exact matching and 1 for matching in reversely ordered mode. The latter comes handy in this example:
N1:O3 sorted descending by numbers
30 Cd
20 Bc
10 Ab
=MATCH(99 ; $N$1:$N$3;1) > #NA (first match fails)
=MATCH(20 ; $N$1:$N$3;1) > 2 (exact match at 2nd positon)
=MATCH(15 ; $N$1:$N$3;1) > 2 (last match at position 2)
Strings:
=MATCH("D" ; $O$1:$O$3;1) > #NA (first match fails)
=MATCH("Bc" ; $O$1:$O$3;1) > 2 (exact match at 2nd positon)
=MATCH("Bb" ; $O$1:$O$3;1) > 2 (last alphabetical match at position 2)
Test MATCH in mode 1, matching descending ordered vectors in combination with INDEX.
Test VLOOKUP in mode 1 and see that it fails.
Test some string lookups in order to see if they match casesensitive or not.
Test some partial strings in order to see if "b" matches with "abc".
5. About #NA and other errors
#NA means that your formula is alright, but the searched value is Not Availlable.
All other errors indicate that there is something wrong with your formula or the formula returns more than a single value (#VALUE).
So #NA is something like a "weak error", which can be converted to zero
Assumed A5 having some matching formula.
=N($A$5) > 0 instead of #NA (a MATCH result is always >=1).
or you can test if a formula is OK, but does not match:
=ISNA($A$5) > TRUE or FALSE where
You can test if there is some serious error in A5:
=ISERR($A$5) > returns FALSE even if A5 is #NA
=ISERROR($A$5) > returns TRUE for all errors
6. Related settings in the options panel Menu:Tools>Options>Calc>Calculation
According to the online help all 4 functions accept pattern matching by regular expressions. This is true if enabled in the panel. When you import an Excel document this option is turned off. Calc assumes that ther are no regular expressions since Excel does not support them.
Turn on the regex option, turn off "Search criteria "=" and "<>" apply to whole cells".
Some strings in D1:D6:
B
b
BcA
Bc
C
c
=MATCH("b"; $D$1:$D$6;0) > 1 (case insensitive match at position 1)
=MATCH("C"; $D$1:$D$6;0) > 3 (letter "c" or "C" found at position 3)
=MATCH("BC";$D$1:$D$6;0) > 3 ("bc" found at position 3)
=MATCH("^bc$"; $D$1:$D$6;0) > 4 (regex: starts with "b", ends with "c" at position 4)
=MATCH("a$"; $D$1:$D$6;0) > 3 (ends with "a" at position 3)
Now turn on "Search criteria "=" and "<>" apply to whole cells", keep regex support
=MATCH("b"; $D$1:$D$6;0) > 1 (same)
=MATCH("C"; $D$1:$D$6;0) > 5 (whole string "C" at position 5)
=MATCH("BC";$D$1:$D$6;0) > 4 (whole string "bc" at position 4)
=MATCH("^bc$"; $D$1:$D$6;0) > 4 (same)
=MATCH("a$"; $D$1:$D$6;0) > #NA (whole string ending with "a" not found)
Now turn off regex support and keep "Search criteria "=" and "<>" apply to whole cells"
=MATCH("b"; $D$1:$D$6;0) > 1 (same)
=MATCH("C"; $D$1:$D$6;0) > 5 (whole string "C" at position 5)
=MATCH("BC";$D$1:$D$6;0) > 4 (whole string "bc" at position 4)
=MATCH("^bc$"; $D$1:$D$6;0) > #NA (whole string "^bc$" not found)
=MATCH("a$"; $D$1:$D$6;0) > #NA (whole string "a$" not found)
Now turn off both options
=MATCH("b"; $D$1:$D$6;0) > 1 (same)
=MATCH("C"; $D$1:$D$6;0) > 3 ("C" found at position 3)
=MATCH("BC";$D$1:$D$6;0) > 3 ("bc" at position 3)
=MATCH("^bc$"; $D$1:$D$6;0) > #NA ("^bc$" not found)
=MATCH("a$"; $D$1:$D$6;0) > #NA ("a$" not found)
7. How to check for sorting?
The following is an array formula. Do not type the curlies and enter with Ctrl+Shift+Enter rather than Enter. The culies will indicate that the formula has been entered as an array function.
Is A1:A100 sorted ascending?
{=SUM($A$1:$A$99>$A$2:$A$100)}
Returnes 0 if no value in A1:A99 is larger than it's successor A2:A100. A results >0 indicates the count of values where the preceeding value is larger than it's successor.
Same descending:
{=SUM($A$1:$A$99<$A$2:$A$100)} _________________ Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Last edited by Villeroy on Fri Jun 01, 2007 8:36 am; edited 2 times in total 

Back to top 


deltareum General User
Joined: 21 Apr 2007 Posts: 10

Posted: Tue May 29, 2007 10:23 am Post subject: 


Thanks for the tutorial. It deserves to be sticky, or saved somewhere. 

Back to top 




You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group
