OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

LOOKUP problem..

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
zafina
Newbie
Newbie


Joined: 27 Jan 2009
Posts: 2

PostPosted: Tue Jan 27, 2009 11:16 pm    Post subject: LOOKUP problem.. Reply with 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..

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jan 28, 2009 1:39 am    Post subject: Reply with quote

"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 https://forum.openoffice.org
Back to top
View user's profile Send private message
remo00
OOo Enthusiast
OOo Enthusiast


Joined: 21 Dec 2006
Posts: 117

PostPosted: Wed Jan 28, 2009 4:55 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jan 28, 2009 5:24 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
zafina
Newbie
Newbie


Joined: 27 Jan 2009
Posts: 2

PostPosted: Fri Jan 30, 2009 12:07 am    Post subject: Reply with quote

i resolved my problem yesterday.. i used an SQL query instead.. Laughing

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$1Razz$2240;O$1:O$2240)

Is this what yuor are looking for?

Cheers


this is exactly what i needed.. thanks.. Very Happy

@Villeroy
tnx for the help too.. Very Happy
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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