• 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

  • chandooMay2019.xlsx
    11 KB · Views: 5
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
 

Attachments

  • LookupTableList.xlsx
    12.2 KB · Views: 9
Last edited:
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

  • chandoo1.xlsx
    41.1 KB · Views: 1
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

  • LookupTableList(BY).xlsx
    42.1 KB · Views: 10
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

  • Copy of LookupTableList(BY)2.xlsx
    42.1 KB · Views: 3
Back
Top