Array | Matrix - Formula???

[aldo jr]

I am in need to time stamp a shift (AM, MD, or PM) according to an
unique identifier (ex. Bus #) and a given time of day. I need to find
x in table 1 while referencing table 2 to gather the correct shift.

-Note: Column Headers in (), fields separated by comma.

where x is the shift.

Table 1 - Determine the correct shift
---------------------------------------------------
(Bus #) , (Time in HH:MM) , Shift
1,7:30, x
2, 9:00, x
3, 14:00, x
..
..
n
---------------------------------------------------

Table 2 - Time Ranges for bus # and their shift
---------------------------------------------------------------
(Bus #) , (Shift) , (Start Time) , (End Time)
1, AM, 7:00, 11:00
1, MD, 11:01, 14:00
1, PM, 14:01, 18:00
2, AM, 6:00, 11:00
2, MD, 11:01, 14:00
2, PM, 14:01, 19:00
3, AM, 6:00, 11:00
3, MD, 11:01, 14:00
3, PM, 14:01, 19:00
..
..
..
n