Excel Formula

[LS]

I have teachers who are teaching subjects at different times. I have a
spreadsheet where I want to lookup the time of day and input what their
teaching. Example

Mary Jones
Start End
Math 8:05 9:20
Science 7:30 8:00
Soc Study 9:25 11:00
Lunch 11:05 11:35

I sort the start dates if that helps.

I have another spreadsheet that shows 7:30, 7:35, 7:40, etc in Column A.
Column B is where I want the data input. Look at column A time (7:30) and
look in spreadsheet above and look for 7:30 and input Math in Column B. Then
on the times that are not show above such as 7:35 how to I made it look at
between the start and end. There may be times when she is free and the field
might be blank with no start or end times.

The spreadsheet should look like

7:30 Science
7:35 Science
7:40 Science
7:45 Science
8:00 Science
8:05 Math

-Is this possible-
LS Teacher

 

[Shane Devenshire]

Hi,

Don't see any dates? Also there is a question about who.

=INDEX(Sheet1!A$2:A$5,MAX((A10>=Sheet1!B$2:B$5)*(A10<=Sheet1!C$2:C$5)*ROW($1:$4)),)

Assume your first range starts on row 2 with titles "Start, End" subjects in
column A. If the times are lised starting in A1 on the second sheet then in
B1 the formula would be the above one.

 

[LS]

I think you close but I didn't explain so well.

I pulled out the info I wanted on the teacher so it doesn't have to look up
the teacher. On your formula what is sheet1 and A10.

Thanks