Mulitple value lookup

[kritter286]

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!

 

[Luke M]

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="Task 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.

 

[Luke M]

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="Task 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.

 

[Roger Govier]

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)

 

[kritter286]

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]

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),"")