Excel Lookup with multiple queries

Question

I have a question that I a may not be thinking correctly about. But I have an a long excel file that I pull from somewhere else with the following columns:

Project_Name1, Employee_Name1, Date_Worked1, Hours_Worked1

In another sheet I have these columns

Project_Name2, Employee_Name2, Begin_Date2, End_Date2, Hours_Worked2

This second sheet is filled with data, and works just fine. However, it turns out that I have some employee names that I do not know that are also working on the same project. I need to figure out the names of the employees and then sum the number of hours they worked for a given period. So I need a lookup with three criteria:

Project_Name1 = Project_Name2

Employee_Name1 <> {Array of Employee_Name2}

Begin_Date2 <= Date_Worked1 > End_Date2

Returning Employee name.

Once I have the employee name, I can do a sumifs=() and get the total hours they worked no problem.

I have tried a number of combinations of Index Match functions, using ctrl-shift-enter... and have not been able to figure out it. Any help would be greatly appreciated.

Answer 1

What you're talking about doing is extremely complicated and a little bit past what Excel was designed to do by default. However, there are a few workarounds that you can use to attempt to get the information that you're looking for.

1. It's possible to do multiple-criteria VLOOKUPs and SUMIFs by concatenating fields to make a multi-part identifier (Ex: Insert a new column and have a forumla in it like =A1&B1)
2. Open a new workbook and use Microsoft Query (I'm not sure if you can select from more than one sheet, but if you can select from multiple sheets like tables you should be able to write a semi-complex query to pull the dataset you want. http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx
3. Use the embedded macro feature and use visual basic script to write out your business logic. (Hotkey is ALT+F11)

Answer 2

One way to do this would be to first create an additional column to the right of entries on the sheet you're trying to pull employee_name from: =ROW()

You could then use an array formula like you were trying to implement to pull the corresponding 'match' row:

{=SUM((project_name1=projectname2)*(employeename1<>employeename2)*(begindate<=date_worked1)*(date_worked1>end_date2)*(match_column))}

You could then use this returned match_column entry within the index as you described to retrieve the appropriate entries.