If Date and Time are within a Range, Return Specific Text

[Mck1308]

In column A, a 24 hour shift begins on the given date, at 07:00:00 AM.

In column B, the corresponding shift is listed.

Given the list of dates and times in column D, how can I return the appropriate shift as shown is column E?

[the color formatting is not necessary]

Screenshot (1).png

Thanks for any help!
Matt

[MrShorty]

Assuming all the date/time stamps are real numbers and not text, it should be a simple lookup with approximate match options (Most lookup type problems start with the VLOOKUP() function. This is a good tutorial since it includes both exact match and approximate match examples: https://www.ablebits.com/office-addi...ximate-vlookup ).

Something like =VLOOKUP(D2,$A$2:$B$7,2,TRUE) [note the mix of relative and absolute references, then copy/paste/fill down] should do the job.

[Mck1308]

Thank you for your reply MrShorty!

After too much time searching the internet, I finally found a formula that works:

Please Login or Register  to view this content.

It helped to add the end of the range in column B.

Screenshot (4).png

[MrShorty]

If you like it, that's fine. This formula is mixing different operations with an exact match lookup, which, if you ever need to do this lookup over a large data set, is going to be very performance heavy. I don't see what it is doing that my simpler approximate match formula is doing. I guess this formula seems to me to be a lot more complicated than it needs to be.