| View previous topic :: View next topic |
| Author |
Message |
nmella
General User
Joined: 25 May 2007
Posts: 14
Location: Chile
|
 Posted: Mon Jun 18, 2007 6:12 am Post subject: VLOOKUP based search on 2 column values ? |
 |
|
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 |
|
 |
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Mon Jun 18, 2007 7:10 am Post subject: |
|
|
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 |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA
|
| Posted: Mon Jun 18, 2007 7:31 am Post subject: |
|
|
| 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 |
|
|
nmella
General User
Joined: 25 May 2007
Posts: 14
Location: Chile
|
| Posted: Mon Jun 18, 2007 7:34 am Post subject: |
|
|
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 |
|
|
nmella
General User
Joined: 25 May 2007
Posts: 14
Location: Chile
|
| Posted: Mon Jun 18, 2007 7:56 am Post subject: |
|
|
| 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 |
|
|
squenson
Super User
Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia
|
| Posted: Mon Jun 18, 2007 12:12 pm Post subject: |
|
|
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 |
|
|
|
