If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Mulitple value lookup
Hello
I am having trouble returning some looked up data. I have several worksheets that I want to pull data from one "User Input" sheet. I am running into a problem when my user input sheet has two or more rows with the same date. For example, my "User Input" sheet looks something like this: Week End Date Task Action Smith Jones Total 3/20/2009 Task 2 Task 2 - Action 2 0.5 1.4 1.9 3/20/2009 Task 2 Task 2 - Action 3 2 0.8 2.8 I have a separate sheet for each task and action. I have a list of dates on each of those sheets. I want to look up the date on the sheet for "Task 2 - Action 2" in the "User Input" sheet, then look up "Task 2 - Action 2" and return the total hours worked for that week. Then, I want to do the same for "Task 2 - Action 3". However, I can not get it to find the second occurance of the date to look up the "Task 2 - Action 3" total. I think it sees the first ocurance, looks for the action, finds "Task 2 - Action 2", and returns "0" since it did not find "Task 2 - Action 3". Right now I am using the IF and VLOOKUP functions. They work just fine as long as I only have 1 occurance of the date. I hope this makes sense. I am using Excel 2003. Thank you for your help! |
#2
|
|||
|
|||
Mulitple value lookup
You might be able to do something with SUMPRODUCT. Perhaps something structed
like: (where A2 = date end you are wanting) =SUMPRODUCT((Sheet1!A2:A100=A2)*(Sheet1!B2:B100="T ask 2")*(Sheet1!F2:F100)) You want to create arrays of true/false conditions, and then ultimately multiply those against the values you want (where only true*true will yield a number) Do note that your array sizes must be equal, and you can only callout an entire column (A:A) if using XL 2007. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "kritter286" wrote: Hello I am having trouble returning some looked up data. I have several worksheets that I want to pull data from one "User Input" sheet. I am running into a problem when my user input sheet has two or more rows with the same date. For example, my "User Input" sheet looks something like this: Week End Date Task Action Smith Jones Total 3/20/2009 Task 2 Task 2 - Action 2 0.5 1.4 1.9 3/20/2009 Task 2 Task 2 - Action 3 2 0.8 2.8 I have a separate sheet for each task and action. I have a list of dates on each of those sheets. I want to look up the date on the sheet for "Task 2 - Action 2" in the "User Input" sheet, then look up "Task 2 - Action 2" and return the total hours worked for that week. Then, I want to do the same for "Task 2 - Action 3". However, I can not get it to find the second occurance of the date to look up the "Task 2 - Action 3" total. I think it sees the first ocurance, looks for the action, finds "Task 2 - Action 2", and returns "0" since it did not find "Task 2 - Action 3". Right now I am using the IF and VLOOKUP functions. They work just fine as long as I only have 1 occurance of the date. I hope this makes sense. I am using Excel 2003. Thank you for your help! |
#3
|
|||
|
|||
Mulitple value lookup
You might be able to do something with SUMPRODUCT. Perhaps something structed
like: (where A2 = date end you are wanting) =SUMPRODUCT((Sheet1!A2:A100=A2)*(Sheet1!B2:B100="T ask 2")*(Sheet1!F2:F100)) You want to create arrays of true/false conditions, and then ultimately multiply those against the values you want (where only true*true will yield a number) Do note that your array sizes must be equal, and you can only callout an entire column (A:A) if using XL 2007. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "kritter286" wrote: Hello I am having trouble returning some looked up data. I have several worksheets that I want to pull data from one "User Input" sheet. I am running into a problem when my user input sheet has two or more rows with the same date. For example, my "User Input" sheet looks something like this: Week End Date Task Action Smith Jones Total 3/20/2009 Task 2 Task 2 - Action 2 0.5 1.4 1.9 3/20/2009 Task 2 Task 2 - Action 3 2 0.8 2.8 I have a separate sheet for each task and action. I have a list of dates on each of those sheets. I want to look up the date on the sheet for "Task 2 - Action 2" in the "User Input" sheet, then look up "Task 2 - Action 2" and return the total hours worked for that week. Then, I want to do the same for "Task 2 - Action 3". However, I can not get it to find the second occurance of the date to look up the "Task 2 - Action 3" total. I think it sees the first ocurance, looks for the action, finds "Task 2 - Action 2", and returns "0" since it did not find "Task 2 - Action 3". Right now I am using the IF and VLOOKUP functions. They work just fine as long as I only have 1 occurance of the date. I hope this makes sense. I am using Excel 2003. Thank you for your help! |
#4
|
|||
|
|||
Mulitple value lookup
Hi
Insert a new column on your Input sheets at Column A with the formula =A1&"!"&B1&"!"&C1 On your Sheet where you are trying to use the lookup, Make your entry in separate cells and use the formula =VLOOKUP(A1&"!"&B1&"!"&C1,'User Input'!$A:G,7,0) -- Regards Roger Govier "kritter286" wrote in message ... Hello I am having trouble returning some looked up data. I have several worksheets that I want to pull data from one "User Input" sheet. I am running into a problem when my user input sheet has two or more rows with the same date. For example, my "User Input" sheet looks something like this: Week End Date Task Action Smith Jones Total 3/20/2009 Task 2 Task 2 - Action 2 0.5 1.4 1.9 3/20/2009 Task 2 Task 2 - Action 3 2 0.8 2.8 I have a separate sheet for each task and action. I have a list of dates on each of those sheets. I want to look up the date on the sheet for "Task 2 - Action 2" in the "User Input" sheet, then look up "Task 2 - Action 2" and return the total hours worked for that week. Then, I want to do the same for "Task 2 - Action 3". However, I can not get it to find the second occurance of the date to look up the "Task 2 - Action 3" total. I think it sees the first ocurance, looks for the action, finds "Task 2 - Action 2", and returns "0" since it did not find "Task 2 - Action 3". Right now I am using the IF and VLOOKUP functions. They work just fine as long as I only have 1 occurance of the date. I hope this makes sense. I am using Excel 2003. Thank you for your help! |
#5
|
|||
|
|||
Mulitple value lookup
My arrays aren't the same size, so I don't think the SUMPRODUCT will work.
However, I think the &"!"& approach will. The only problem is that if there is not an entry on a date, it returns #N/A, and I need it to return "0". Any pointers there? "Roger Govier" wrote: Hi Insert a new column on your Input sheets at Column A with the formula =A1&"!"&B1&"!"&C1 On your Sheet where you are trying to use the lookup, Make your entry in separate cells and use the formula =VLOOKUP(A1&"!"&B1&"!"&C1,'User Input'!$A:G,7,0) -- Regards Roger Govier "kritter286" wrote in message ... Hello I am having trouble returning some looked up data. I have several worksheets that I want to pull data from one "User Input" sheet. I am running into a problem when my user input sheet has two or more rows with the same date. For example, my "User Input" sheet looks something like this: Week End Date Task Action Smith Jones Total 3/20/2009 Task 2 Task 2 - Action 2 0.5 1.4 1.9 3/20/2009 Task 2 Task 2 - Action 3 2 0.8 2.8 I have a separate sheet for each task and action. I have a list of dates on each of those sheets. I want to look up the date on the sheet for "Task 2 - Action 2" in the "User Input" sheet, then look up "Task 2 - Action 2" and return the total hours worked for that week. Then, I want to do the same for "Task 2 - Action 3". However, I can not get it to find the second occurance of the date to look up the "Task 2 - Action 3" total. I think it sees the first ocurance, looks for the action, finds "Task 2 - Action 2", and returns "0" since it did not find "Task 2 - Action 3". Right now I am using the IF and VLOOKUP functions. They work just fine as long as I only have 1 occurance of the date. I hope this makes sense. I am using Excel 2003. Thank you for your help! |
#6
|
|||
|
|||
Mulitple value lookup
Hi
for XL2003 and earlier =IF(ISERROR(VLOOKUP(A1&"!"&B1&"!"&C1,'User Input'!$A:G,7,0)),"", VLOOKUP(A1&"!"&B1&"!"&C1,'User Input'!$A:G,7,0)) for XL2007 =IFERROR(VLOOKUP(A1&"!"&B1&"!"&C1,'User Input'!$A:G,7,0),"") -- Regards Roger Govier "kritter286" wrote in message ... My arrays aren't the same size, so I don't think the SUMPRODUCT will work. However, I think the &"!"& approach will. The only problem is that if there is not an entry on a date, it returns #N/A, and I need it to return "0". Any pointers there? "Roger Govier" wrote: Hi Insert a new column on your Input sheets at Column A with the formula =A1&"!"&B1&"!"&C1 On your Sheet where you are trying to use the lookup, Make your entry in separate cells and use the formula =VLOOKUP(A1&"!"&B1&"!"&C1,'User Input'!$A:G,7,0) -- Regards Roger Govier "kritter286" wrote in message ... Hello I am having trouble returning some looked up data. I have several worksheets that I want to pull data from one "User Input" sheet. I am running into a problem when my user input sheet has two or more rows with the same date. For example, my "User Input" sheet looks something like this: Week End Date Task Action Smith Jones Total 3/20/2009 Task 2 Task 2 - Action 2 0.5 1.4 1.9 3/20/2009 Task 2 Task 2 - Action 3 2 0.8 2.8 I have a separate sheet for each task and action. I have a list of dates on each of those sheets. I want to look up the date on the sheet for "Task 2 - Action 2" in the "User Input" sheet, then look up "Task 2 - Action 2" and return the total hours worked for that week. Then, I want to do the same for "Task 2 - Action 3". However, I can not get it to find the second occurance of the date to look up the "Task 2 - Action 3" total. I think it sees the first ocurance, looks for the action, finds "Task 2 - Action 2", and returns "0" since it did not find "Task 2 - Action 3". Right now I am using the IF and VLOOKUP functions. They work just fine as long as I only have 1 occurance of the date. I hope this makes sense. I am using Excel 2003. Thank you for your help! |
Thread Tools | |
Display Modes | |
|
|