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

VLOOKUP based search on 2 column values ?

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


Joined: 25 May 2007
Posts: 14
Location: Chile

PostPosted: Mon Jun 18, 2007 6:12 am    Post subject: VLOOKUP based search on 2 column values ? Reply with quote

Hi,

Is it possible to search with VLOOKUP based on 2 column searchs ?

For example:



I need to search column A for a specific Job Number. Then from those Job Numbers I need to select a specific event ("c" event in this case).

Can VLOOKUP do this? Or how should I do it ?

Thanks for any advice!
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Jun 18, 2007 7:10 am    Post subject: Reply with quote

First of all, with two criteria [columns A and B] to be used for the search, what is left to look for that depends upon them? a date?

David.
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Mon Jun 18, 2007 7:31 am    Post subject: Reply with quote

You can do this with the DGET function. When in Help be sure to look at the example table at the top of the help page. You can copy and paste this into Calc then delete the top row and left column so the formulas in the examples work.
Back to top
View user's profile Send private message
nmella
General User
General User


Joined: 25 May 2007
Posts: 14
Location: Chile

PostPosted: Mon Jun 18, 2007 7:34 am    Post subject: Reply with quote

Maybe I wrote a little bit confusing.....

Instead of:

=VLOOKUP("job number" and "c event";A2:C13;2)

I meant:

=VLOOKUP("job number" and "c type";A2:C13;2)

So I need to extract the date "event" of column C, considering a "job number" from column A and a particular event from column B.

Thanks
Back to top
View user's profile Send private message
nmella
General User
General User


Joined: 25 May 2007
Posts: 14
Location: Chile

PostPosted: Mon Jun 18, 2007 7:56 am    Post subject: Reply with quote

JohnV wrote:
You can do this with the DGET function. When in Help be sure to look at the example table at the top of the help page. You can copy and paste this into Calc then delete the top row and left column so the formulas in the examples work.


Thank you.

But with DGET I will only have 1 result for a specific search. (You are right, this is what I have asked)

But I need to populate this result down for evey record of "a type" Better to understand with an example:

At every row where there is a "a" type record from column B, I need to put the date of the "c" type record for the corresponding job number:

[url][/url]

Thanks again.
Back to top
View user's profile Send private message
squenson
Super User
Super User


Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

PostPosted: Mon Jun 18, 2007 12:12 pm    Post subject: Reply with quote

nmella,

My solution is a little bit complex, but I think it works based on the conditions you described.

1) Insert a column JobEvent to the right of Event and to the left of date with formula in C2: = A2 & B2 and copy it down

2) In the column E (previously column D) use the formula for D2:
Code:
=IF(B2="a"; IF(N(MATCH(A2&"c";$C$2:$C$15;0)); VLOOKUP(A2&"c";$C$2:$D$15;2;0); ""); "")
The IF(N(MATCH(...)) is there to test the presence of a row of type "c", so we can be sure that the VLOOKUP will return the exact value. As you may know VLOOKUP returns the last found value if an exact match is not found, which may give strange results in your case.
_________________
Help us to help you: Add [Solved] to the title of the thread if you agree with the answer
>>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
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