Vlookup that only returns a value that satifies a condition.

[JohnLVand]

Hello, hope someone can help here. I've been hacking away on this one for days.
I'm looking for some code that can do a Vlookup (or other function) that
will do the following. Rather than trying to describe the logic, I thought
it may be easier to show you what has to be done..

The following data has been placed into a worksheet called "Jobs"
Row # 1 are titles.

A B C D
1 Site# Job# Name Status
2 a123 2005-012 KL Complete
3 90652 2003-120 NN Current
4 4b00v1 2007-001 AB Complete
5 8008 2006-234 PN Pending
6 a123 2007-123 KL Current
7 h7799 2004-222 KL Current
8 90652 2007-901 NN Complete
....
500 v456 2007-867 PN Current

I have another worksheet called "Projects"
In this worksheet I need to see the following "Current" projects ...

A B C
Site# Job# Name
1 90652 2003-120 NN
2 a123 2007-123 KL
3 h7799 2004-222 KL
4 v456 2007-867 PN


I would like to have cells in column "B" and "C" filled with values from
Worksheet "Jobs" only if the row has a Status of "Current".
The only information that I need to key in is Column "A" (Site #).
Columns "B" and "C" only get filled in if the condition is satisfied.

Any ideas or help would be most appreciated !

JLV

 

[bj]

try in projects!B1
=if(vlookup(A1,Jobs!A,4,0)="Current",vlookup(A1,Jobs!A,3,0))
or if what is in column A on projects may not be in column A Job
=if(iserror(vlookup(A1,Jobs!A,4,0),"",if(vlookup(A1,Jobs!A,4,0),="Current",vlookup(A1,Jobs!A,3,0),""))

 

[Toppers]

In column B:

=INDEX(Jobs!$B$2:$B$8,MATCH(1,(Jobs!$A$2:$A$8=A2)*(Jobs!$D$2:$D$8="Current"),0))

in column C:

=INDEX(Jobs!$C$2:$C$8,MATCH(1,(Jobs!$A$2:$A$8=A2)*(Jobs!$D$2:$D$8="Current"),0))

Enter both with Ctrl+Shift+Enter

 

[JohnLVand]

bj,
Thanks for the quick answer.
I tried something like this previously but the first occurrence of any Site
# was returned first, rather than drilling down to satisfy the condition.
I can't sort worksheet "jobs" since it's already in a priority sequence.
(not sure if that would make a difference though)

In your solution below....
ie: If I key in "a123" in cell "projects!a1" ...it returns a blank in column
"B".
It should return a "2007-123", which is the job number for the "current"
site called "a123"
There will be multiple occurrences of site#'s and some of them will be
"Current" while others, not.
Sorry if I didn't make this clear the first time.

You did however tweek another idea I have for something else.
Thanks

 

[JohnLVand]

Thanks Toppers for the quick response...
And Kudos...This is exactly what I was looking for.
I tried the vlookups, and tinkered with the Index function, but I just
couldn't figure out the arrays and references.

One other thing though....
I get a #N/A in columns "B" and "C" for rows that do not have a site # keyed
in.
So I put an IF ISERROR VLOOKUP function in to get rid of the #N/A
Seems to work just fine.(using the ctrl/shift/enter)

=IF(ISERROR(VLOOKUP(A2,Jobs!$A$2:$D$8,1,0)),"--",(INDEX(Jobs!$B$2:$B$8,MATCH(1,(Jobs!$A$2:$A$8=A2)*(Jobs!$D$2:$D$8="Current"),0))))

Thanks again.

 

[JohnLVand]

Update here.
The IF ISERROR VLOOKUP doesn't work as I thought.
It picks up the first occurence of the Site# regardless of status.
I will just do without, or should I say "do" with the #N/A .

Thanks

 

[Toppers]

Try:

=IF(ISNA(INDEX(Jobs!$B$2:$B$8,MATCH(1,(Jobs!$A$2:$A$8=A2)*(Jobs!$D$2:$D$8="Current"),0))),"",INDEX(Jobs!$B$2:$B$8,MATCH(1,(Jobs!$A$2:$A$8=A2)*(Jobs!$D$2:$D$8="Current"),0)))

 

[bj]

try somethig like in B1

=if(sumproduct(--(Jobs!A$:A$100=A1),--(Jobs!D$1$100="Current"))=1,sumproduct(--(Jobs!A$1:A$100=A1),--(Jobs!D$1$100="Current")),Jobs!B$1:B$100),"")
and in C1

=if(sumproduct(--(Jobs!A$1:A$100=A1),--(Jobs!D$1$100="Current"))=1,sumproduct(--(Jobs!A$1:A$100=A1),--(Jobs!D$1$100="Current")),Jobs!C$1:C$100),"")

this will get the top one equal to A1 and "current"
If there are more than one match , how do you want the format of the answer.

 

[JohnLVand]

Works Great !
That cleans things up quite nicely and looks much more professional.
Thanks for the help, it's much appreciated !
Regards
JohnLVan

 

[JohnLVand]

Interesting solution...I will give that one a try too.

To answer your question about "more than one match", the Job# is unique and
will be either "current" or something else (pending or complete).
The data in the Jobs worksheet is a total history and will have multiple
Job#'s that point to the same Site#. However, there will only be one
occurence of a Site# that is current. The Projects worksheet is a summary of
"sub-projects" that are current.
Hope that explains it.
Thanks
JohnLVand