Xlookup or index & match

[mipasodu]

Hi, I have tried to use vlookup, xlookup (nested) and Index and match but I'm making a mess. I need to lookup a worker's name from a list and then return the most recent date that matches the course name. sample data attached

Course Log.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	!! Data exported from course system in A1:H50				!! Workers can repeat courses every 1 or 2 years			Workers might have done every course, every year since employment began				I need the most recent course date in M2:T11	Course A	Course B	Course C	Course D	Course E	Course F	Course G	Course H
2	Worker	Status	Role	CC	Course	TR Status	Year	Completed				Worker 1
3	Worker 1	Active	Factory	1234	Course A	Completed	2019	4.3.2019				Worker 2
4	Worker 1	Active	Factory	1234	Course A	Completed	2020	1.2.2020				Worker 3
5	Worker 1	Active	Factory	1234	Course A	Completed	2021	20.1.2021				Worker 4
6	Worker 1	Active	Factory	1234	Course B	Completed	2021	4.3.2021				Worker 5
7	Worker 1	Active	Factory	1234	Course G	Completed	2020	10.1.2020
8	Worker 1	Active	Factory	1234	Course B	Completed	2020	19.12.2020
9	Worker 1	Active	Factory	1234	Course C	Completed	2019	27.8.2019
10	Worker 1	Active	Factory	1234	Course C	Completed	2021	6.8.2021
11	Worker 2	Active	Factory	1234	Course A	Completed	2020	14.8.2020
12	Worker 2	Active	Factory	1234	Course A	Completed	2020	20.4.2020
13	Worker 2	Active	Factory	1234	Course A	Completed	2021	15.8.2021
14	Worker 2	Active	Factory	1234	Course B	Completed	2021	2.1.2021
15	Worker 2	Active	Factory	1234	Course A	Completed	2022	7.9.2022
16	Worker 2	Active	Factory	1234	Course A	Completed	2022	7.9.2022
17	Worker 3	Active	Factory	1234	Course H	Completed	2021	15.8.2021
18	Worker 3	Active	Factory	1234	Course B	Completed	2023	2.1.2023
19	Worker 3	Active	Factory	1234	Course B	Completed	2021	1.9.2021
20	Worker 3	Active	Factory	1234	Course B	Completed	2022	8.10.2022
21	Worker 3	Active	Factory	1234	Course C	Completed	2022	1.7.2022
22	Worker 3	Active	Factory	1234	Course F	Completed	2022	8.7.2022
23	Worker 3	Active	Factory	1234	Course A	Completed	2021	25.6.2021
24	Worker 3	Active	Factory	1234	Course A	Completed	2019	28.8.2019
25	Worker 3	Active	Factory	1234	Course B	Completed	2021	18.3.2021
26	Worker 3	Active	Factory	1234	Course E	Completed	2019	27.8.2019
27	Worker 3	Active	Factory	1234	Course B	Completed	2021	6.8.2021
28	Worker 3	Active	Factory	1234	Course C	Completed	2020	14.8.2020
29	Worker 3	Active	Factory	1234	Course C	Completed	2020	20.4.2020
30	Worker 3	Active	Factory	1234	Course A	Completed	2021	15.8.2021
31	Worker 3	Active	Factory	1234	Course A	Completed	2021	25.6.2021
32	Worker 3	Active	Factory	1234	Course A	Completed	2019	28.8.2019
33	Worker 4	Active	Factory	1234	Course B	Completed	2021	18.3.2021
34	Worker 4	Active	Factory	1234	Course B	Completed	2019	27.8.2019
35	Worker 4	Active	Factory	1234	Course B	Completed	2021	6.8.2021
36	Worker 4	Active	Factory	1234	Course C	Completed	2020	14.8.2020
37	Worker 4	Active	Factory	1234	Course C	Completed	2020	20.4.2020
38	Worker 4	Active	Factory	1234	Course A	Completed	2021	15.8.2021
39	Worker 4	Active	Factory	1234	Course A	Completed	2021	2.1.2021
40	Worker 5	Active	Factory	1234	Course A	Completed	2022	7.9.2022
41	Worker 5	Active	Factory	1234	Course D	Completed	2022	7.9.2022
42	Worker 5	Active	Factory	1234	Course B	Completed	2020	14.8.2020
43	Worker 5	Active	Factory	1234	Course B	Completed	2020	20.4.2020
44	Worker 5	Active	Factory	1234	Course C	Completed	2021	15.8.2021
45	Worker 5	Active	Factory	1234	Course C	Completed	2021	2.1.2021
46	Worker 5	Active	Factory	1234	Course A	Completed	2022	7.9.2022
47	Worker 5	Active	Factory	1234	Course A	Completed	2022	7.9.2022
48	Worker 5	Active	Factory	1234	Course A	Completed	2021	15.8.2021
49	Worker 5	Active	Factory	1234	Course B	Completed	2021	2.1.2021
50	Worker 5	Active	Factory	1234	Course B	Completed	2021	1.9.2021
Sheet1

Cell Formulas
Range	Formula
G3:G50	G3	=H3

 

[mipasodu]

I currently have "=INDEX(H3:H50;MATCH(L2;A3:A50;0))" in M2 but I need to add something to the formula to make it look for the most recent date.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1													Course A	Course B	Course C	Course D	Course E	Course F	Course G	Course H
2	Worker	Status	Role	CC	Course	TR Status	Year	Completed				Worker 1	20/01/2021	19/12/2020	06/08/2021				10/01/2020
3	Worker 1	Active	Factory	1234	Course A	Completed	2019	04/03/2019				Worker 2	07/09/2022	02/01/2021
4	Worker 1	Active	Factory	1234	Course A	Completed	2020	01/02/2020				Worker 3	28/08/2019	06/08/2021	20/04/2020		27/08/2019	08/07/2022		15/08/2021
5	Worker 1	Active	Factory	1234	Course A	Completed	2021	20/01/2021				Worker 4	02/01/2021	06/08/2021	20/04/2020
6	Worker 1	Active	Factory	1234	Course B	Completed	2021	04/03/2021				Worker 5	15/08/2021	01/09/2021	02/01/2021	07/09/2022
7	Worker 1	Active	Factory	1234	Course G	Completed	2020	10/01/2020
8	Worker 1	Active	Factory	1234	Course B	Completed	2020	19/12/2020
9	Worker 1	Active	Factory	1234	Course C	Completed	2019	27/08/2019
10	Worker 1	Active	Factory	1234	Course C	Completed	2021	06/08/2021
11	Worker 2	Active	Factory	1234	Course A	Completed	2020	14/08/2020
12	Worker 2	Active	Factory	1234	Course A	Completed	2020	20/04/2020
13	Worker 2	Active	Factory	1234	Course A	Completed	2021	15/08/2021
14	Worker 2	Active	Factory	1234	Course B	Completed	2021	02/01/2021
15	Worker 2	Active	Factory	1234	Course A	Completed	2022	07/09/2022
16	Worker 2	Active	Factory	1234	Course A	Completed	2022	07/09/2022
17	Worker 3	Active	Factory	1234	Course H	Completed	2021	15/08/2021
18	Worker 3	Active	Factory	1234	Course B	Completed	2023	02/01/2023
19	Worker 3	Active	Factory	1234	Course B	Completed	2021	01/09/2021
20	Worker 3	Active	Factory	1234	Course B	Completed	2022	08/10/2022
21	Worker 3	Active	Factory	1234	Course C	Completed	2022	01/07/2022
22	Worker 3	Active	Factory	1234	Course F	Completed	2022	08/07/2022
23	Worker 3	Active	Factory	1234	Course A	Completed	2021	25/06/2021
24	Worker 3	Active	Factory	1234	Course A	Completed	2019	28/08/2019
25	Worker 3	Active	Factory	1234	Course B	Completed	2021	18/03/2021
26	Worker 3	Active	Factory	1234	Course E	Completed	2019	27/08/2019
27	Worker 3	Active	Factory	1234	Course B	Completed	2021	06/08/2021
28	Worker 3	Active	Factory	1234	Course C	Completed	2020	14/08/2020
29	Worker 3	Active	Factory	1234	Course C	Completed	2020	20/04/2020
30	Worker 3	Active	Factory	1234	Course A	Completed	2021	15/08/2021
31	Worker 3	Active	Factory	1234	Course A	Completed	2021	25/06/2021
32	Worker 3	Active	Factory	1234	Course A	Completed	2019	28/08/2019
33	Worker 4	Active	Factory	1234	Course B	Completed	2021	18/03/2021
34	Worker 4	Active	Factory	1234	Course B	Completed	2019	27/08/2019
35	Worker 4	Active	Factory	1234	Course B	Completed	2021	06/08/2021
36	Worker 4	Active	Factory	1234	Course C	Completed	2020	14/08/2020
37	Worker 4	Active	Factory	1234	Course C	Completed	2020	20/04/2020
38	Worker 4	Active	Factory	1234	Course A	Completed	2021	15/08/2021
39	Worker 4	Active	Factory	1234	Course A	Completed	2021	02/01/2021
40	Worker 5	Active	Factory	1234	Course A	Completed	2022	07/09/2022
41	Worker 5	Active	Factory	1234	Course D	Completed	2022	07/09/2022
42	Worker 5	Active	Factory	1234	Course B	Completed	2020	14/08/2020
43	Worker 5	Active	Factory	1234	Course B	Completed	2020	20/04/2020
44	Worker 5	Active	Factory	1234	Course C	Completed	2021	15/08/2021
45	Worker 5	Active	Factory	1234	Course C	Completed	2021	02/01/2021
46	Worker 5	Active	Factory	1234	Course A	Completed	2022	07/09/2022
47	Worker 5	Active	Factory	1234	Course A	Completed	2022	07/09/2022
48	Worker 5	Active	Factory	1234	Course A	Completed	2021	15/08/2021
49	Worker 5	Active	Factory	1234	Course B	Completed	2021	02/01/2021
50	Worker 5	Active	Factory	1234	Course B	Completed	2021	01/09/2021
Master

Cell Formulas
Range	Formula
M2:T6	M2	=IFERROR(INDEX($H:$H,AGGREGATE(14,6,ROW($A$2:$A$50)/($E$2:$E$50=M$1)/($A$2:$A$150=$L2),1)),"")
G3:G50	G3	=H3

 

[mipasodu]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1													Course A	Course B	Course C	Course D	Course E	Course F	Course G	Course H
2	Worker	Status	Role	CC	Course	TR Status	Year	Completed				Worker 1	20/01/2021	19/12/2020	06/08/2021				10/01/2020
3	Worker 1	Active	Factory	1234	Course A	Completed	2019	04/03/2019				Worker 2	07/09/2022	02/01/2021
4	Worker 1	Active	Factory	1234	Course A	Completed	2020	01/02/2020				Worker 3	28/08/2019	06/08/2021	20/04/2020		27/08/2019	08/07/2022		15/08/2021
5	Worker 1	Active	Factory	1234	Course A	Completed	2021	20/01/2021				Worker 4	02/01/2021	06/08/2021	20/04/2020
6	Worker 1	Active	Factory	1234	Course B	Completed	2021	04/03/2021				Worker 5	15/08/2021	01/09/2021	02/01/2021	07/09/2022
7	Worker 1	Active	Factory	1234	Course G	Completed	2020	10/01/2020
8	Worker 1	Active	Factory	1234	Course B	Completed	2020	19/12/2020
9	Worker 1	Active	Factory	1234	Course C	Completed	2019	27/08/2019
10	Worker 1	Active	Factory	1234	Course C	Completed	2021	06/08/2021
11	Worker 2	Active	Factory	1234	Course A	Completed	2020	14/08/2020
12	Worker 2	Active	Factory	1234	Course A	Completed	2020	20/04/2020
13	Worker 2	Active	Factory	1234	Course A	Completed	2021	15/08/2021
14	Worker 2	Active	Factory	1234	Course B	Completed	2021	02/01/2021
15	Worker 2	Active	Factory	1234	Course A	Completed	2022	07/09/2022
16	Worker 2	Active	Factory	1234	Course A	Completed	2022	07/09/2022
17	Worker 3	Active	Factory	1234	Course H	Completed	2021	15/08/2021
18	Worker 3	Active	Factory	1234	Course B	Completed	2023	02/01/2023
19	Worker 3	Active	Factory	1234	Course B	Completed	2021	01/09/2021
20	Worker 3	Active	Factory	1234	Course B	Completed	2022	08/10/2022
21	Worker 3	Active	Factory	1234	Course C	Completed	2022	01/07/2022
22	Worker 3	Active	Factory	1234	Course F	Completed	2022	08/07/2022
23	Worker 3	Active	Factory	1234	Course A	Completed	2021	25/06/2021
24	Worker 3	Active	Factory	1234	Course A	Completed	2019	28/08/2019
25	Worker 3	Active	Factory	1234	Course B	Completed	2021	18/03/2021
26	Worker 3	Active	Factory	1234	Course E	Completed	2019	27/08/2019
27	Worker 3	Active	Factory	1234	Course B	Completed	2021	06/08/2021
28	Worker 3	Active	Factory	1234	Course C	Completed	2020	14/08/2020
29	Worker 3	Active	Factory	1234	Course C	Completed	2020	20/04/2020
30	Worker 3	Active	Factory	1234	Course A	Completed	2021	15/08/2021
31	Worker 3	Active	Factory	1234	Course A	Completed	2021	25/06/2021
32	Worker 3	Active	Factory	1234	Course A	Completed	2019	28/08/2019
33	Worker 4	Active	Factory	1234	Course B	Completed	2021	18/03/2021
34	Worker 4	Active	Factory	1234	Course B	Completed	2019	27/08/2019
35	Worker 4	Active	Factory	1234	Course B	Completed	2021	06/08/2021
36	Worker 4	Active	Factory	1234	Course C	Completed	2020	14/08/2020
37	Worker 4	Active	Factory	1234	Course C	Completed	2020	20/04/2020
38	Worker 4	Active	Factory	1234	Course A	Completed	2021	15/08/2021
39	Worker 4	Active	Factory	1234	Course A	Completed	2021	02/01/2021
40	Worker 5	Active	Factory	1234	Course A	Completed	2022	07/09/2022
41	Worker 5	Active	Factory	1234	Course D	Completed	2022	07/09/2022
42	Worker 5	Active	Factory	1234	Course B	Completed	2020	14/08/2020
43	Worker 5	Active	Factory	1234	Course B	Completed	2020	20/04/2020
44	Worker 5	Active	Factory	1234	Course C	Completed	2021	15/08/2021
45	Worker 5	Active	Factory	1234	Course C	Completed	2021	02/01/2021
46	Worker 5	Active	Factory	1234	Course A	Completed	2022	07/09/2022
47	Worker 5	Active	Factory	1234	Course A	Completed	2022	07/09/2022
48	Worker 5	Active	Factory	1234	Course A	Completed	2021	15/08/2021
49	Worker 5	Active	Factory	1234	Course B	Completed	2021	02/01/2021
50	Worker 5	Active	Factory	1234	Course B	Completed	2021	01/09/2021
Master

Cell Formulas
Range	Formula
M2:T6	M2	=IFERROR(INDEX($H:$H,AGGREGATE(14,6,ROW($A$2:$A$50)/($E$2:$E$50=M$1)/($A$2:$A$150=$L2),1)),"")
G3:G50	G3	=H3

Yes that works, I need to change it slightly for the live sheet but your formula was perfect, thank you

 

[Fluff]

You're welcome & thanks for the feedback.