Match formulae
 

I have the following data in one worksheet. I have to bring the prog name
from other wrksheet wherein the time in Colmn E is between start time & end
time given in the other wrksheet.
1st Wkshet
D E
07:20:15
08:37:24
09:38:07
06:38:34
07:01:05

2nd Wkshet
A B C
From To Sunday
06:45 07:00 GURU
07:00 07:30 SPL RRT
07:30 07:45 Bhani
07:45 08:00 NWS
08:00 08:30 SPL RRT
08:30 08:45 Bhani
08:45 09:00 SUINA

I used the following formulae but doesn't get the desired result.

=INDEX('[FPC FEB 08.xls]Sheet1'!$C$2:$C$8,MATCH(1,('[FPC FEB
08.xls]Sheet1'!$A$2:$A$8E1)*('[FPC FEB 08.xls]Sheet1'!$B$2:$B$8<E1),0))

can anyone help.

thanks in advance

Match formulae
 

Assuming the source times in E1:E5 are real times
Try this revision, array-entered** in say, F1:
=INDEX('[FPC Feb 08.xls]Sheet1'!$C$2:$C$8,MATCH(1,('[FPC Feb
08.xls]Sheet1'!$A$2:$A$8<=E1)*('[FPC Feb 08.xls]Sheet1'!$B$2:$B$8=E1),0))
Copy F2 down to F6

**Press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"shaji" wrote:
I have the following data in one worksheet. I have to bring the prog name
from other wrksheet wherein the time in Colmn E is between start time & end
time given in the other wrksheet.
1st Wkshet
D E
07:20:15
08:37:24
09:38:07
06:38:34
07:01:05

2nd Wkshet
A B C
From To Sunday
06:45 07:00 GURU
07:00 07:30 SPL RRT
07:30 07:45 Bhani
07:45 08:00 NWS
08:00 08:30 SPL RRT
08:30 08:45 Bhani
08:45 09:00 SUINA

I used the following formulae but doesn't get the desired result.

=INDEX('[FPC FEB 08.xls]Sheet1'!$C$2:$C$8,MATCH(1,('[FPC FEB
08.xls]Sheet1'!$A$2:$A$8E1)*('[FPC FEB 08.xls]Sheet1'!$B$2:$B$8<E1),0))

can anyone help.

thanks in advance