Index Function Avoiding Duplicates From Dynamic Array

Occasional Visitor

Hello Everyone, 

 

 

I'm working on a sheet for work and cannot seem to figure this out. I'm hoping someone can offer some help:

 

I have a dynamic data sheet with all my data on the same page. On this sheet I would like it to return all the names matching this criteria without repeating the same one (I drag the formula down and some show up two or three times). My current formula works but it returns duplicates. My formula is as follows: 

 

=IFERROR(@INDEX($R$5:$R$100,AGGREGATE(15,3,(($Q$5:$Q$100=$B$17)/($Q$5:$Q$100=$B$17)*ROW($Q$5:$Q$100))-ROW($Q$4),ROWS($R$4:R5))),"")

 

B17= where the criteria is (needs to fall under US LARGE CAP) 

Q5-Q100= the list of criteria (US LARGE CAP, CASH, ETC) 

R5-R100 = The data I am looking to have returned (Stock Tickers) 

 

Is there any way to rewrite this formula to return a list of only unique values without duplicate returns? 

I would prefer not to have to make any adjustments to the data but open to suggestions! 

1 Reply

@Microsoftexceluser29 

Abolish the INDEX() solution.

=UNIQUE(FILTER(R5:R100,Q5:Q100=B17,""))