Excel search with multiple queries

Question

I have a question that I may not be thinking right about. But I have a long excel file that I am extracting from another place 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 great. However, it turns out that I have names of employees who I don’t know who are also working on the same project. I need to find out the names of employees, and then summarize the number of hours during which they worked for a certain period. So I need a search with three criteria:

Project_Name1 = Project_Name2

Employee_Name1 <> {Array of Employee_Name2}

Begin_Date2 <= Date_Worked1> End_Date2

The returned name of the employee.

Once I have the name of the employee, I can do sumifs = () and get the total number of hours during which they did not work.

I tried several combinations of Match Match functions using ctrl-shift-enter ... and couldn't figure it out. Any help would be greatly appreciated.

Answer 1

What you are talking about is extremely complicated and a little earlier than Excel was created by default. However, there are a few workarounds that you can use to try and get the information you are looking for.

• It is possible to make several criteria VLOOKUP and SUMIF by combining the fields to make a multi-component identifier (for example: insert a new column and have forumla in it, for example = A1 & B1)
• Open a new book and use Microsoft Query (I'm not sure if you can select one or several sheets, but if you can choose from several sheets, for example tables, you will have to write a semi-complex query to pull out the dataset that you want. Http : //office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx
• Use the built-in macro function and use the visual base script to write out your business logic. (Hotkey ALT + F11)

Answer 2

One way to do this would be to first create an additional column to the right of the entries in the sheet that you are trying to extract from the name employee_name: =ROW()

Then you can use the array formula, as you tried to implement, to output the corresponding matching string:

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

Then you can use this returned match_column string in the index as described to get the matching records.