display result on a list based on a given table/data

[tango]

looking/searching for a right formula that will display a cell on a list based on a given table.

A match result will be based on the following criteria:
1) date
2) timing
3) name

if it match then it display the time start and time end on a separate list/table. I have attached the sample file which shows the desired results. I believe it is understandable and better explained when the file is open.

Thank you for your help

 

[bosco_yip]

In J13, copied across and down :

=INDEX($C$1:$E$13,MATCH($H13,INDEX($C$1:$E$13,0,SUMPRODUCT(($C$1:$E$13=$H13)*COLUMN($A$1:$C$1))),0)+2*RIGHT($I13)+COLUMN(A$1)-2,SUMPRODUCT(($C$1:$E$13=$H13)*COLUMN($A$1:$C$1)))

Regards
Bosco

 

[tango]

Thanks Bosco. PM sent.

 

[tango]

Hi Bosco,

It works however I am not able to put it together due to worksheets I have in my actual file. I attached here a new document where you may please incorporate the formula once again please. Thanks for your help.

 

[bosco_yip]

The formula logic is as same as the above, only a bit of changing as in :

C2, copied across and down :

=INDEX('4staffschedule'!$C$1:$G$800,MATCH($A2,INDEX('4staffschedule'!$C$1:$G$800,0,SUMPRODUCT(('4staffschedule'!$C$1:$G$800=$A2)*COLUMN($A$1:$E$1))),0)+MATCH($B2,'4staffschedule'!$A$3:$A$22,0)+COLUMN(A$1)-1,SUMPRODUCT(('4staffschedule'!$C$1:$G$800=$A2)*COLUMN($A$1:$E$1)))

Regards
Bosco

 

[tango]

Thanks Bosco.... This is great but I forgot to add the "Sunday" column... sorry for this and appreciate if you could update it please.

I tried "=INDEX('4StaffSchedule'!$C$1:$H$800,MATCH($A727,INDEX('4StaffSchedule'!$C$1:$H$800,0,SUMPRODUCT(('4StaffSchedule'!$C$1:$H$800=$A727)*COLUMN($A$1:$F$1))),0)+MATCH($B727,'4StaffSchedule'!$A$3:$A$22,0)+COLUMN(A$1)-1,SUMPRODUCT(('4StaffSchedule'!$C$1:$H$800=$A727)*COLUMN($A$1:$F$1))) "

but it did not work out. Please help.