Find the nth occurrence of a word, to find the column_num, to find the student name.

[Dave_O]

Hello,
I am running a spreadsheet that carries over the student name to the next round when they "Pass" - when a result is correct.

I've been trying to use index/small/match to find the nth occurrence of the word "Pass", to find the column_num, to find the student name.

cell E12 returns an #NUM! error with {=INDEX($C$1:$V$1,,SMALL(MATCH("Pass",$C$8:$V$8,0),2))}


Row 12 student name, these students pass round one:
C12 = Barbara (first occurrence of Pass)
E12 = David (second occurrence of Pass)
G12 = James (third occurrence of Pass)
I12 = Jennifer (fourth occurrence of Pass)
K12 = John (fifth occurrence of Pass)


Row 21 student name, these students pass round two:
C21 = David (first occurrence of Pass)
E21 = Jennifer (second occurrence of Pass)
G21 = John (third occurrence of Pass)

Students who pass move to next round.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Student Name	Barbara	Result	Charles	Result	David	Result	Elizabeth	Result	James	Result	Jennifer	Result	Jessica	Result	John	Result
2	Round 1	Answer 1	11	Correct	37	Incorrect	78	Incorrect	15	Incorrect	88	Incorrect	4	Incorrect	28	Incorrect	1	Incorrect
3	Round 1	Answer 2	13	Incorrect	43	Incorrect	75	Correct	80	Incorrect	37	Incorrect	35	Incorrect	41	Incorrect	78	Incorrect
4	Round 1	Answer 3	30	Incorrect	62	Incorrect	65	Incorrect	75	Incorrect	79	Correct	73	Incorrect	74	Incorrect	83	Incorrect
5	Round 1	Answer 4	65	Incorrect	71	Incorrect	51	Incorrect	29	Incorrect	26	Incorrect	2	Correct	49	Incorrect	81	Incorrect
6	Round 1	Answer 5	46	Incorrect	22	Incorrect	48	Incorrect	11	Incorrect	44	Incorrect	31	Incorrect	77	Incorrect	74	Correct
7	Round 1	Answer 6	24	Incorrect	63	Incorrect	67	Incorrect	21	Incorrect	57	Incorrect	33	Incorrect	72	Incorrect	54	Incorrect
8	Final Result		Pass		Fail		Pass		Fail		Pass		Pass		Fail		Pass
9
10
11			Find 1st Pass	Barbara	Find 2nd Pass	David	Find 3rd Pass	James	Find 4th Pass	Jennifer	Find 5th Pass	John
12		Student Name	Barbara	Result	#NUM!	Result	#NUM!	Result	#NUM!	Result	#NUM!	Result
13	Round 2	Answer 1	72	Incorrect	6	Correct	49	Incorrect	66	Incorrect	26	Incorrect
14	Round 2	Answer 2	83	Incorrect	98	Incorrect	59	Incorrect	42	Correct	47	Incorrect
15	Round 2	Answer 3	97	Incorrect	22	Incorrect	39	Incorrect	20	Incorrect	93	Correct
16	Round 2	Answer 4	49	Incorrect	13	Incorrect	32	Correct	76	Incorrect	48	Incorrect
17	Final Result		Fail		Pass		Pass		Pass		Pass
18
19
20			Find 1st Pass	David	Find 2nd Pass	James	Find 3rd Pass	Jennifer	Find 4th Pass	John
21		Student Name	#NUM!	Result		Result		Result		Result
22	Round 3	Answer 1	44	Correct	72	Inccorect	46	Inccorect	28	Inccorect
23	Round 3	Answer 2	31	Inccorect	76	Inccorect	79	Correct	74	Inccorect
24	Round 3	Answer 3	76	Correct	26	Inccorect	100	Inccorect	67	Inccorect
25	Round 3	Answer 4	67	Inccorect	74	Inccorect	98	Correct	99	Inccorect
26	Final Result		Pass		Fail		Pass		Fail
27
28
29
30
Sheet1

Cell Formulas
Range	Formula
C8,Q8,O8,M8,K8,I8,G8,E8	C8	=IF(COUNTIF(D2:D7,"Correct")>0,"Pass","Fail")
C12	C12	=INDEX($C$1:$R$1,,SMALL(MATCH("Pass",$C$8:$R$8,0),1))
E12	E12	=INDEX($C$1:$R$1,,SMALL(MATCH("Pass",$C$8:$R$8,0),2))
G12	G12	=INDEX($C$1:$R$1,,SMALL(MATCH("Pass",$C$8:$R$8,0),3))
I12	I12	=INDEX($C$1:$R$1,,SMALL(MATCH("Pass",$C$8:$R$8,0),4))
K12	K12	=INDEX($C$1:$R$1,,SMALL(MATCH("Pass",$C$8:$R$8,0),5))
C17,I26,G26,E26,C26,K17,I17,G17,E17	C17	=IF(COUNTIF(D13:D16,"Correct")>0,"Pass","Fail")
C21	C21	=INDEX($C$12:$L$12,,SMALL(MATCH("Pass",$C$17:$L$17,0),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

Try
C12
=INDEX($C$1:$R$1,AGGREGATE(15,6,(COLUMN($C$1:$R$1)-COLUMN($C$1)+1)/($C$8:$R$8="Pass"),1))
E12
=INDEX($C$1:$R$1,AGGREGATE(15,6,(COLUMN($C$1:$R$1)-COLUMN($C$1)+1)/($C$8:$R$8="Pass"),2))
G12
=INDEX($C$1:$R$1,AGGREGATE(15,6,(COLUMN($C$1:$R$1)-COLUMN($C$1)+1)/($C$8:$R$8="Pass"),3))
and so on

C21
=INDEX($C$12:$L$12,AGGREGATE(15,6,(COLUMN($C$12:$L$12)-COLUMN($C$12)+1)/($C$17:$L$17="Pass"),1))
E21
=INDEX($C$12:$L$12,AGGREGATE(15,6,(COLUMN($C$12:$L$12)-COLUMN($C$12)+1)/($C$17:$L$17="Pass"),2))
and so on

M.

 

[Dave_O]

Thank you so much, Marcelo.
I have not come across AGGREGATE before and I will study up tonight on its functionality, for future uses.

Thanks for showing me something new!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.