Lookup time value based on criteria

**wafs** · 02-14-2019, 12:20 PM

Hello Excel masters!

I hope someone can help me out. I'm currently running out of ideas on how to make this work.

I need to find an approximate time on a given date per specific employee based on their logs. My goal is to lookup for a range of time that falls under another range of time. Unfortunately, the formula that I currently use only works if I add 1 employee's data on file but if there's multiple, that's when I get random lookup values.

Please see the attached file for more details. Formula is in "Qatch" worksheet under column H.

Please Login or Register  to view this content.

I'm open to suggestions such as adding columns to expand the criteria since I'm dealing with 160 employees with 600 to 1000 rows of data for each.

The sample Excel file only contains 2 employees but it's already inaccurate.

Any help would be appreciated

**XLent** · 02-14-2019, 02:08 PM

LOOKUP, when used in this context, relies on the lookup_vector being sorted in Asc order and this isn't the case in your file.

If, as implied, your file is going to be big then I would still advocate LOOKUP as it will give you massive performance gains BUT you must sort your Raw Data tab by PEIN & Date Treated (or simply Combine_Qatch if this column is a Constant rather than being derived from [PEIN]&[DateTreated])

Once you've done that, revert to your Qatch tab and review results -- if not as expected please post back and advise expected results... it's hard to discern what the expected results are from your initial post.

**dflak** · 02-14-2019, 02:30 PM

First of all, congratulations on a well-laid-out data set AND in an Excel Table no less!

I think I have what you want. On the Control Panel sheet, you can define the start and end dates and times in the green shaded cells where indicated.

On the Raw Interval sheet, I added some helper columns to compute the date-time (date + start time) and the length of the call. I had to convert the length of call into a string because I can't set the cell format where it is in the pivot table. I also have a column called In Range to determine if the start date and time and end date and time for a record is within the times specified on the Control Panel sheet.

I then made a pivot table on the results. I created some agents to demonstrate the use of slicers to filter the data to look up specific agents.