• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

tango

Member
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
 

Attachments

tango

Member
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.
 

Attachments

bosco_yip

Excel Ninja
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
 

Attachments

tango

Member
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.
 

Attachments

Top