is they a way to index match more than once?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,458
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,759
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,253
Messages
5,467,563
Members
406,543
Latest member
semoredhawk

This Week's Hot Topics

Top