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

#### Attachments

• 11 KB Views: 5

#### bosco_yip

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

• 12.2 KB Views: 9
Last edited:

#### tango

##### Member
Thanks Bosco. PM sent.

#### 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

• 41.1 KB Views: 1

#### 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

• 42.1 KB Views: 10

#### 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))) "