Data Extracting using Greater Than/Less Than

[pop]

I am trying to lookup up the attendance day and time in the Attendance Table (on the top) within the program table (on the bottom) and return the program title in the attendance table (in the shaded area).

If the hours fall outside of the program tables starttime and or finishtime an empty cell is returned.

Any help with a formula would be greatly appreciated

Attendance Table
Day Arrival Time Program
Tuesday 16:00 Program 1
Monday 15:20 Program 2
Thursday 15:30 Program 2
Thursday 13:00 Program 5
Monday 12:00
Monday 16:30 Program3
Tuesday 11:00

Program Table
Program Day StartTime FinishTime
Program 1 Tuesday 15:30 17:00
Program 2 Thursday 15:30 17:00
Program 3 Monday 14:30 17:00
Program 4 Wednesday 15:30 17:30
Program 5 Thursday 12:00 13:30

 

[Nebu]

Hi:
Is this what you are looking for?

=IFERROR(INDEX($F$1:$F$7,SUMPRODUCT((A3=$G$3:$G$7)*(B3>=$H$3:$H$7)*(B3<=$I$3:$I$7)*ROW($F$3:$F$7)),0),"")

Thanks

 

[Deepak]

@ pop

There's sims an error at row 4.



result should be Program 3.

@ Nebu, You can't use simply sumproduct in that way! as if there's no matching value it produces 0 that will index first/last value.

Another approach CSE

=IFERROR(INDEX($F$3:$F$7,MATCH(1,(A3=$G$3:$G$7)*(B3>=$H$3:$H$7)*(B3<=$I$3:$I$7),0)),"")

 

[Deepak]

Another approach!

=IFERROR(LOOKUP(2,1/((A3=$G$3:$G$7)*(B3>=$H$3:$H$7)*(B3<=$I$3:$I$7)),$F$3:$F$7),"")

 

[pop]

thank you all for you quick reply i will check them all out shortlty

 

[Nebu]

@Deepak , Yea I missed that in a hurry.

Thanks