Problem looking up shift from encoded cell with date/time?

I am trying to do a vlookup to find the work shift a cell entry occured during.

The cell with the time I want to lookup also has a date. For example, the cell has "11/2/2010 12:57:24". Using the TIME function and functions for hour, minutes and seconds, I can extract from the cell only the time portion.

But, then I run into a problem doing the vlookup using this time against a table that has for 24 hours what time periods are covered by 1st, 2nd and 3rd shifts.

Please review the attachment and input the correct formula to lookup the shift in tab "DATA" cell "C2" or column C, based on if the time is equal or between the start and end time of each shift.


Thank you,


BennyB

Try:

=INDEX('LTB-SHIFT'!$C$2:$C$5,MATCH(1,INDEX(('LTB-SHIFT'!$A$2:$A$5<=MOD(B2,1))*('LTB-SHIFT'!$B$2:$B$5>=MOD(B2,1)),0),0))

copied down.