| View previous topic :: View next topic |
| Author |
Message |
zafina
Newbie
Joined: 27 Jan 2009
Posts: 2
|
 Posted: Tue Jan 27, 2009 11:16 pm Post subject: LOOKUP problem.. |
 |
|
i have 54583 records for the search criterion.. i pasted the lookup table on the the whole column O & P.. so that it will reach the very last row (65536).. the lookup works for some, and gets an #N/A error on some of the search_criterion..
code:
=LOOKUP(D2;P1:P2240;O1:2240)
this works fine on the first cell..
however.. when i drag this till the next cell.. which is D3the search_vector & result_vectors value also increments..
=LOOKUP(D3;P2:P2241;O2:2241)
how can i lock the values for the two vectors?
so that i will remain "P1:P2240;O1:2240 " as the search criterion increments.. |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Jan 28, 2009 1:39 am Post subject: |
|
|
"Records" is a database term and you are using a spreadsheet. You try to fake a databsae relation in a spreadsheet by means of lookups. This is an impossible thing to do, particularly if you are completely new to spreadsheets.
1. Your references are all relative, so they adjust with every row.
2. LOOKUP and it's friends are designed to match things like grades in ordered ranges of points. MATCH, VLOOKUP and HLOOKUP allow for exact lookups in unordered search vectors if you explicitly tell them to do so. LOOKUP can not do what you seemingly want to do.
3. You are not aware of the subtile differences when matching strings in spreadsheets. At least you do not mention if you try matching text or numbers.
All this is no issue at all when using a relational database.
[Tutorial] Absolute, relative and mixed references
Re: [Solved] LOOKUP merged cells problem (has nothing to do with merging)
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
remo00
OOo Enthusiast
Joined: 21 Dec 2006
Posts: 117
|
| Posted: Wed Jan 28, 2009 4:55 am Post subject: |
|
|
You simply need to say to calc not to move the reference
This is done by the "$" sign
The comand is something like this
=LOOKUP(D2;P$1:P$2240;O$1:O$2240)
Is this what yuor are looking for?
Cheers |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Jan 28, 2009 5:24 am Post subject: |
|
|
| Quote: | | i have 54583 records for the search criterion.. i pasted the lookup table on the the whole column O & P.. so that it will reach the very last row (65536).. the lookup works for some, and gets an #N/A error on some of the search_criterion.. |
Adding the absolute row references solves part #1 of the problem. It does not solve the problem that LOOKUP will return wrong results from unordered search vectors and LOOKUP will return false positives from sorted search vectors:
| Code: |
=LOOKUP(0;{0;1;2}) 0
=LOOKUP(0.5;{0;1;2}) 0
=LOOKUP(1;{0;1;2}) 1
=LOOKUP(1.5;{0;1;2}) 1
=LOOKUP(2;{0;1;2}) 2
=LOOKUP(999;{0;1;2}) 2
=LOOKUP(-1;{0;1;2}) #N/A
|
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
zafina
Newbie
Joined: 27 Jan 2009
Posts: 2
|
| Posted: Fri Jan 30, 2009 12:07 am Post subject: |
|
|
i resolved my problem yesterday.. i used an SQL query instead.. 
i guess i was too late to check my post here..
| remo00 wrote: | You simply need to say to calc not to move the reference
This is done by the "$" sign
The comand is something like this
=LOOKUP(D2;P$1 $2240;O$1:O$2240)
Is this what yuor are looking for?
Cheers |
this is exactly what i needed.. thanks.. 
@Villeroy
tnx for the help too.. |
|
| Back to top |
|
|
|
