Thread: Excel Lookup with Multiple Queries - Complex Index Match Function

0 down vote favorite


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.

I have attached a replica of your problem. I started getting confused between which sheet had the erroneous records, but I believe I have captured a solution. I have added a new column to sheet2 with the array formula to total matches from sheet1. If there is no match, the result will be zero. The zero will be your indicator that the record is missing from sheet1.

Goodluck,
Dionysos