Index Match Formula Shows Wrong Result

[Enjoylyfe]

I have two table, this one is the initial table that contains raw data (on Sheet 2)

coba final.xlsx
	A	B	C	D	E	F
1	No	User	Date	Time	Activity	Description
2	A-1	Staff 3	05/06/2020	9:00	test	test
3	A-1	Staff 3	05/06/2020	9:30	test	test
4	A-1	Staff 3	05/06/2020	10:00	test	test
5	A-1	Staff 3	05/06/2020	10:30	test	test
6	A-3	Staff 1	30/04/2020	10:00	t1	t1
7	A-3	Staff 1	30/04/2020	10:30	t1	t1
8	A-3	Staff 1	30/04/2020	11:00	t1	t1
9	A-3	Staff 1	30/04/2020	11:30	t1	t1
10	A-4	Staff 6	30/04/2020	8:30	test	test1
11	A-4	Staff 6	30/04/2020	9:00	test	test1
12	A-4	Staff 6	30/04/2020	9:30	test	test1
Sheet2

And the second table (on Sheet 1) contains formula based on data from first table

coba final.xlsx
	A	B	C	D	E	F	G	H
1		30-Apr-20
2		Staff 1	Staff 2	Staff 3	Staff 4	Staff 5	Staff 6	Staff 7
3	8:30
4	9:00						test
5	9:30
6	10:00
7	10:30	t1
8	11:00
9	11:30
10	12:00
11	12:30
12	13:00
13	13:30
14	14:00
15	14:30
16	15:00
17	15:30
18	16:00
19	16:30
20	17:00
21	17:30
Sheet1

Cell Formulas
Range	Formula
B3:H21	B3	=IFERROR(INDEX(Sheet2!$E$2:$E$12,MATCH(Sheet1!$B$1&Sheet1!B$2&Sheet1!$A3,Sheet2!$C$2:$C$12&Sheet2!$B$2:$B$12&Sheet2!$D$2:$D$12,0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

But as we can see on the table, the formula doesn't produce right result. Could you please help me to modify the formula?

 

[Peter_SSs]

Most likely a rounding error with the times.
Also, best not to use the sheet name of the sheet that your formula is on. It isn't needed and can lead to errors when sorting etc.

Try this in B3 of Sheet1 and copy across and down.
=IFERROR(INDEX(Sheet2!$E$2:$E$12,MATCH($B$1&B$2&ROUND($A3,6),Sheet2!$C$2:$C$12&Sheet2!$B$2:$B$12&ROUND(Sheet2!$D$2:$D$12,6),0)),"")

.. or if your 365 version has the FILTER function you could also try
=FILTER(Sheet2!$E$2:$E$12,(Sheet2!$C$2:$C$12=$B$1)*(Sheet2!$B$2:$B$12=B$2)*(ROUND(Sheet2!$D$2:$D$12,6)=ROUND($A3,6)),"")

 

[Enjoylyfe]

Most likely a rounding error with the times.
Also, best not to use the sheet name of the sheet that your formula is on. It isn't needed and can lead to errors when sorting etc.

Try this in B3 of Sheet1 and copy across and down.
=IFERROR(INDEX(Sheet2!$E$2:$E$12,MATCH($B$1&B$2&ROUND($A3,6),Sheet2!$C$2:$C$12&Sheet2!$B$2:$B$12&ROUND(Sheet2!$D$2:$D$12,6),0)),"")

.. or if your 365 version has the FILTER function you could also try
=FILTER(Sheet2!$E$2:$E$12,(Sheet2!$C$2:$C$12=$B$1)*(Sheet2!$B$2:$B$12=B$2)*(ROUND(Sheet2!$D$2:$D$12,6)=ROUND($A3,6)),"")

It works, thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.