I have two table, this one is the initial table that contains raw data (on Sheet 2)
And the second table (on Sheet 1) contains formula based on data from first table
But as we can see on the table, the formula doesn't produce right result. Could you please help me to modify the formula?
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?