is they a way to index match more than once?

[tonywatsonhelp]

Hi Everyone,

I'm try to make a list based on a name selected

I'm getting stuck so please help if you can

Column A has names in it Repeated lots of time with column be having interests,

	A	B	C	D	E	F	G	H	I
1	nAME	Interests	Sub		Chosen Name		Unique List	G is what i'm trying to create from A and b
2	tony	Film	Horry		tony		Film
3	bob	film					Books
4	bob	film
5	sue	books
6	tony	Books
7	tony	Books
8	bob	books
9	sue	film
10	sue	film
11	bob	books
12	tony	Film	Comedy
13	bob	film
14	sue	film
15	dave	books
	dave	film

<tbody>
</tbody>

I want to create a list of just the unique interests in column b in column J

 

[Eric W]

Put this in G2:

=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$16=$E$2,IF(COUNTIF($G$1:$G1,$B$2:$B$16)=0,ROW($A$2:$A$16))),1)),"")

change the ranges to match your sheet, and confirm with Control+Shift+Enter, not just enter. Then drag it down the column.

I'm not sure what you want in column J? Is it a list of all the interests, regardless of the person? If so,

=IFERROR(INDEX(B:B,SMALL(IF(COUNTIF($J$1:$J1,$B$2:$B$16)=0,ROW($B$2:$B$16)),1)),"")

in J2 with CSE.

 

[tonywatsonhelp]

Hi Eric,

This is great, thank you very much
Tony

 

[Eric W]

You're welcome!