Complicated lookup

New Contributor

I have tried using v/xlookup or index&match to return for shift start time (will do the same for shift end) based on events in a shift for multiple employees. Simple normally, but these return the first instance and I need to return the correct shift for the correct employee based on the event time/day (some shifts cross midnight…)


I can only presume I need some kind of loop or if statement to keep looking if the event does not fall within the first shift?


Been troubling me for days and deadlines are looming! Any help much appreciated, happy to explain further.



2 Replies


Does the attached example match what you want to do?

Not quite, I probably didn’t explain very well! Thanks for giving it a go. Couldn’t find a simple way of doing I now have a long formula including a couple of IFs, ISERROR, VLOOKUP, and FILTER!