What is the best formula to use

[Linsie]

Hi,
I have a sheet where I need to collect the completion dates of a training course per course title for each engineer.

What would be the best formula to match both the engineer name and the course title and return the completion date please.

Thanks in advance.

Training Tracker 2024.xlsx
	A	B	C	D	E	F
1	Engineer Name	Course Name 1	Course Name 2	Course Name 3	Course Name 4
2	Joe Bloggs 1
3	Joe Bloggs 2
4	Joe Bloggs 3
5	Joe Bloggs 4
6	Joe Bloggs 5
7	Joe Bloggs 6
8
9
10
11
12
13
14
15	Data Sheet
16	Full Name	Course	Enroll Status	Enrollment Date	Completion Date
17	Joe Bloggs 1	Course Name 2	Completed	01/01/2024	31/03/2024
18	Joe Bloggs 2	Course Name 1	Started	02/01/2024	05/04/2024
19	Joe Bloggs 3	Course Name 4	Completed	03/01/2024	03/04/2024
20	Joe Bloggs 4	Course Name 1	Completed	04/01/2024	01/01/2024
21	Joe Bloggs 5	Course Name 3	Completed	05/01/2024	29/02/2024
22
23
24
Sheet2

 

[shift-del]

Like this?

Mappe5
	A	B	C	D	E
1	Engineer Name	Course Name 1	Course Name 2	Course Name 3	Course Name 4
2	Joe Bloggs 1		31.03.2024
3	Joe Bloggs 2	05.04.2024
4	Joe Bloggs 3				03.04.2024
5	Joe Bloggs 4	01.01.2024
6	Joe Bloggs 5			29.02.2024
7	Joe Bloggs 6
Tabelle1

Cell Formulas
Range	Formula
B2:E7	B2	=FILTER(Data[Completion Date],(Data[Full Name]=$A2)*(Data[Course]=B$1),"")

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E
1	Engineer Name	Course Name 1	Course Name 2	Course Name 3	Course Name 4
2	Joe Bloggs 1		31/03/2024
3	Joe Bloggs 2	05/04/2024
4	Joe Bloggs 3				03/04/2024
5	Joe Bloggs 4	01/01/2024
6	Joe Bloggs 5			29/02/2024
7	Joe Bloggs 6
8
9
10
11
12
13
14
15	Data Sheet
16	Full Name	Course	Enroll Status	Enrollment Date	Completion Date
17	Joe Bloggs 1	Course Name 2	Completed	01/01/2024	31/03/2024
18	Joe Bloggs 2	Course Name 1	Started	02/01/2024	05/04/2024
19	Joe Bloggs 3	Course Name 4	Completed	03/01/2024	03/04/2024
20	Joe Bloggs 4	Course Name 1	Completed	04/01/2024	01/01/2024
21	Joe Bloggs 5	Course Name 3	Completed	05/01/2024	29/02/2024
Sheet6

Cell Formulas
Range	Formula
B2:E7	B2	=IFERROR(1/(1/SUMIFS($E$17:$E$21,$A$17:$A$21,$A2,$B$17:$B$21,B$1)),"")

 

[Cubist]

Book1
	A	B	C	D	E
1	Engineer Name	Course Name 1	Course Name 2	Course Name 3	Course Name 4
2	Joe Bloggs 1		3/31/2024
3	Joe Bloggs 2	4/5/2024
4	Joe Bloggs 3				4/3/2024
5	Joe Bloggs 4	1/1/2024
6	Joe Bloggs 5			2/29/2024
7	Joe Bloggs 6
8
9
10
11
12
13
14
15	Data Sheet
16	Full Name	Course	Enroll Status	Enrollment Date	Completion Date
17	Joe Bloggs 1	Course Name 2	Completed	1/1/2024	3/31/2024
18	Joe Bloggs 2	Course Name 1	Started	1/2/2024	4/5/2024
19	Joe Bloggs 3	Course Name 4	Completed	1/3/2024	4/3/2024
20	Joe Bloggs 4	Course Name 1	Completed	1/4/2024	1/1/2024
21	Joe Bloggs 5	Course Name 3	Completed	1/5/2024	2/29/2024
Sheet1

Cell Formulas
Range	Formula
B2:E7	B2	=IFERROR(INDEX(E17:E21,MATCH(A2:A7&B1:E1,A17:A21&B17:B21,0)),"")
Dynamic array formulas.

 

[Linsie]

Thanks all. That has just saved me hours of work!

 

[Fluff]

Glad we could help & thanks for the feedback.