Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
try in projects!B1
=if(vlookup(A1,Jobs!A:D,4,0)="Current",vlookup(A1, Jobs!A:D,3,0)) or if what is in column A on projects may not be in column A Jobs =if(iserror(vlookup(A1,Jobs!A:D,4,0),"",if(vlookup (A1,Jobs!A:D,4,0),="Current",vlookup(A1,Jobs!A:D,3 ,0),"")) "JohnLVand" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
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 "bj" wrote: try in projects!B1 =if(vlookup(A1,Jobs!A:D,4,0)="Current",vlookup(A1, Jobs!A:D,3,0)) or if what is in column A on projects may not be in column A Jobs =if(iserror(vlookup(A1,Jobs!A:D,4,0),"",if(vlookup (A1,Jobs!A:D,4,0),="Current",vlookup(A1,Jobs!A:D,3 ,0),"")) "JohnLVand" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
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. "Toppers" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
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 "JohnLVand" wrote: 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. "Toppers" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
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(J obs!$B$2:$B$8,MATCH(1,(Jobs!$A$2:$A$8=A2)*(Jobs!$D $2:$D$8="Current"),0))) "JohnLVand" wrote: 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 "JohnLVand" wrote: 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. "Toppers" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
try somethig like in B1
=if(sumproduct(--(Jobs!A$:A$100=A1),--(Jobs!D$1:D$100="Current"))=1,sumproduct(--(Jobs!A$1:A$100=A1),--(Jobs!D$1:D$100="Current")),Jobs!B$1:B$100),"") and in C1 =if(sumproduct(--(Jobs!A$1:A$100=A1),--(Jobs!D$1:D$100="Current"))=1,sumproduct(--(Jobs!A$1:A$100=A1),--(Jobs!D$1:D$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" wrote: 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 "bj" wrote: try in projects!B1 =if(vlookup(A1,Jobs!A:D,4,0)="Current",vlookup(A1, Jobs!A:D,3,0)) or if what is in column A on projects may not be in column A Jobs =if(iserror(vlookup(A1,Jobs!A:D,4,0),"",if(vlookup (A1,Jobs!A:D,4,0),="Current",vlookup(A1,Jobs!A:D,3 ,0),"")) "JohnLVand" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
Works Great !
That cleans things up quite nicely and looks much more professional. Thanks for the help, it's much appreciated ! Regards JohnLVan "Toppers" wrote: 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(J obs!$B$2:$B$8,MATCH(1,(Jobs!$A$2:$A$8=A2)*(Jobs!$D $2:$D$8="Current"),0))) "JohnLVand" wrote: 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 "JohnLVand" wrote: 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. "Toppers" wrote: 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" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup that only returns a value that satifies a condition.
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 "bj" wrote: try somethig like in B1 =if(sumproduct(--(Jobs!A$:A$100=A1),--(Jobs!D$1:D$100="Current"))=1,sumproduct(--(Jobs!A$1:A$100=A1),--(Jobs!D$1:D$100="Current")),Jobs!B$1:B$100),"") and in C1 =if(sumproduct(--(Jobs!A$1:A$100=A1),--(Jobs!D$1:D$100="Current"))=1,sumproduct(--(Jobs!A$1:A$100=A1),--(Jobs!D$1:D$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" wrote: 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 "bj" wrote: try in projects!B1 =if(vlookup(A1,Jobs!A:D,4,0)="Current",vlookup(A1, Jobs!A:D,3,0)) or if what is in column A on projects may not be in column A Jobs =if(iserror(vlookup(A1,Jobs!A:D,4,0),"",if(vlookup (A1,Jobs!A:D,4,0),="Current",vlookup(A1,Jobs!A:D,3 ,0),"")) "JohnLVand" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup returns a zero? | Excel Discussion (Misc queries) | |||
VLookup returns #VALUE! | Excel Worksheet Functions | |||
vlookup returns 0.00 | Links and Linking in Excel | |||
Condition now returns error | Excel Worksheet Functions | |||
vlookup returns n/a | Excel Worksheet Functions |