Formula que busque un valor y verifique si se encuentra en un rango.

Hola, necesito una formula con la
cual me busque el valor de una celda en un rango de celdas, y luego confirme si
el valor se encuentra según un
rango proporcionado por otras celdas. Creo que lo anterior esta algo confuso, pero ahora pongo un ejemplo.

Tengo dos hojas de Excel,
Hoja 1 tengo los valores siguientes
Columna A Columna B
3:00 Luis
18:00 Carlos
15:00 Luis
24:00 Mario
00:00 Luis
07:00 Octavio

Hoja 2 en las columnas A, B y C tengo los valores abajo.
Columna A Columna B Columna C
1:00 5:00 Luis
3:00 8:00 Carlos
15:00 20:00 Juan
1:00 2:00 Luis
18:00 22:00 Ramón
14:00 17:00 Luis

En la columna D de la hoja 2 quiero coloque el resultado de lo siguiente
De la hoja 2 tome el valor de C1 que es Luis, este valor lo busque en la columna B de la hoja 1, si encuentra ese valor, tome el valor que haya en la columna A de la fila en donde encontró el valor, que en el primer caso sería 3:00 luego compruebe si este valor se encuentra entre el rango de las columnas A y B de la hoja 2, si está en ese rango me
coloque si y si no está coloque no.

Lo anterior se haga con todos los valores de la columna C de la hoja 2
Espero haya sido lo más claro
posible, y de antemano gracias por la ayuda.