Guys,
I've been trying to use different types of formulas that can help me find the right data on a data set, but have so far failed to find one that works. The closest I got was an index/match formula, that only answered part of my issue. Here's what I need:
I have one data set (see attached) which contains five pieces of information: a date, start time, end time, zone number, and status. On data set 2, I have a list of times that I need to find in data set 1, and figure out what that zone's status was at that date and time. So, I need to "lookup" the status for each row in data set 2, based on matching the date, the zone, and the row that contains the right time range (e.g., if the timestamp in data set 2 is 10am, it should find a record in data set 1 that has a start time equal to or greater than 10am, and an end time that is equal to or lesser than 10am).
I've also tried concatenating the date and zone, so that I only look up with three criteria (date/zone, >=start time, and <=end time), but that didn't work.
Please help!!
Status comparison.xlsxStatus comparison.xlsx
Thanks
Bookmarks