+ Reply to Thread
Results 1 to 2 of 2

Excel Lookup with Multiple Queries - Complex Index Match Function

  1. #1
    Registered User
    Join Date
    07-27-2009
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    13

    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.

  2. #2
    Forum Contributor
    Join Date
    08-04-2011
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    109

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

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1