Complicated Formula ?

[mcintoshmc]

I have a spreadsheet that lists all of our candidates by name and the corresponding manager. It also includes the position number they are interviewing for.

On a separate sheet, I have the position number in cell i7, I'd like $j8 to show the candidates name. If there have been 10 interviews for this position number, then i'd like j8:j17 to display those 10 names.

The sheet containing the info:

Column A = Position Number
Column H = Name of Candidate

 

[Aryatect]

Hi, Are you looking for something like this:

Book1
	A	B	C	D	E	F	G
1	Position	Candidate Name				Position
2	1	A				1	A, D, G
3	2	B
4	3	C
5	1	D
6	2	E
7	3	F
8	1	G
9	2	H
10	3	I
Sheet4

Cell Formulas
Range	Formula
G2	{=TEXTJOIN(", ",1,IF($A$2:$A$10=$F2,$B$2:$B$10,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mcintoshmc]

Actually, I figured it out. I used the below as an ARRAY. Thanks.

=IF(ISERROR(INDEX('[MASTER RECRUITMENT V.1.xlsm]Interviews'!$A$1:$O$1000,SMALL(IF('[MASTER RECRUITMENT V.1.xlsm]Interviews'!$A$1:$A$1000=$I$7,ROW('[MASTER RECRUITMENT V.1.xlsm]Interviews'!$A$1:$A$1000)),ROW(1:1)),8)),"",INDEX('[MASTER RECRUITMENT V.1.xlsm]Interviews'!$A$1:$O$1000,SMALL(IF('[MASTER RECRUITMENT V.1.xlsm]Interviews'!$A$1:$A$1000=$I$7,ROW('[MASTER RECRUITMENT V.1.xlsm]Interviews'!$A$1:$A$1000)),ROW(1:1)),8))