Formula to return Pending and In Progress Courses

[Sufiyan97]

I need a formula to return pending or in progress course name for each employee, below is a small example for what I want

Book3
	A	B	C	D	E	F	G	H	I
1		Courses
2	Name	A1	A2	A3	A4	A5	A6	A7
3	A	Completed	Pending	Completed	In Progress	Completed	Pending	Completed
4	B	Completed	Completed	Completed	Completed	Completed	Completed	Completed
5	C	Completed	Completed	Completed	Completed	Completed	Completed	Completed
6	D	Completed	Completed	Completed	Completed	Completed	Completed	Completed
7	E	Pending	Completed	In Progress	Completed	Completed	Completed	Pending
8	F	Completed	Completed	Completed	Completed	Completed	Completed	Completed
9	G	Completed	Completed	Completed	Completed	Completed	Completed	Completed
10	H	Completed	Completed	Completed	Completed	Completed	Completed	Completed
11	I	Completed	Completed	Completed	Completed	Completed	Completed	Completed
12	J	Completed	Completed	Completed	Completed	Completed	Completed	Completed
13
14
15	Expected Result (Pending and In Progress)
16
17	Name
18	A	A2	A4	A6
19	E	A1	A3	A7
20
21
Sheet1

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1		Courses
2	Name	A1	A2	A3	A4	A5	A6	A7
3	A	Completed	Pending	Completed	In Progress	Completed	Pending	Completed
4	B	Completed	Completed	Completed	Completed	Completed	Completed	Completed
5	C	Completed	Completed	Completed	Completed	Completed	Completed	Completed
6	D	Completed	Completed	Completed	Completed	Completed	Completed	Completed
7	E	Pending	Completed	In Progress	Completed	Completed	Completed	Pending
8	F	Completed	Completed	Completed	Completed	Completed	Completed	Completed
9	G	Completed	Completed	Completed	Completed	Completed	Completed	Completed
10	H	Completed	Completed	Completed	Completed	Completed	Completed	Completed
11	I	Completed	Completed	Completed	Completed	Completed	Completed	Completed
12	J	Completed	Completed	Completed	Completed	Completed	Completed	Completed
13
14
15	Expected Result (Pending and In Progress)
16
17	Name
18	A	A2	A4	A6
19	E	A1	A3	A7
Sheet6

Cell Formulas
Range	Formula
B18:D19	B18	=LET(f,FILTER($B$3:$H$12,$A$3:$A$12=A18),FILTER($B$2:$H$2,(f="pending")+(f="in progress")))
Dynamic array formulas.

 

[Sufiyan97]

Hi Fluff,

Thank you very much, this works perfect.

If the data is like below, and I just want courses name and not certificate, can you please help on that

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1		Courses
2	Name	A1	Certificate	A2	Certificate	A3	Certificate	A4	A5	A6	A7
3	A	Completed	Pending	Pending	Completed	Completed	Pending	In Progress	Completed	Pending	Completed
4	B	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed
5	C	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed
6	D	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed
7	E	Pending	Completed	Completed	In Progress	In Progress	Completed	Completed	Completed	Completed	Pending
8	F	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed
9	G	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed
10	H	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed
11	I	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed
12	J	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed	Completed
13
14
15	Expected Result (Pending and In Progress)
16
17	Name
18	A	A2	A4	A6
19	E	A1	A3	A7
20
Sheet1

 

[Fluff]

How about

=LET(f,FILTER($B$3:$K$12,$A$3:$A$12=A18),FILTER($B$2:$K$2,((f="pending")+(f="in progress"))*($B$2:$K$2<>"Certificate")))

 

[Sufiyan97]

That worked perfect, thank you very much Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.