Eliminate duplicates in a dynamic list

[excelbytes]

I use this formula structure or a variation of it to extract a dynamic list from a data range or table:

=IFERROR(INDEX($A$2:$A$26,SMALL(IF(($B$2:$B$26=$H$1)*($C$2:$C$26>=$I$1),ROW($A$2:$A$26)),ROW(1:1))-1,1),"")

The question has come up, how to ensure the results have no duplicates. I know we can add a helper column to the data range that would include a COUNTIF function and then add a criteria where the value is only 1, but is there a way to accomplish this within the formula itself?

Thanks.

 

[Eric W]

If you have the new array functions, then just:

=UNIQUE(FILTER(A2:A26,(B2:B26=H1)*(C2:C26>=I1)))

If not, and your formula is in F2, then:

=IFERROR(INDEX($A$2:$A$26,AGGREGATE(15,6,(ROW($A$2:$A$26)-ROW($A$2)+1)/($B$2:$B$26=$H$1)/($C$2:$C$26>=$I$1)/(COUNTIF($F$1:$F1,$A$2:$A$26)=0),1)),"")

 

[excelbytes]

Eric W,

Thanks. Yes, it's much simpler with the UNIQUE and FILTER functions, however, not all have that option. I did try your other formula and it resulted in the first instance being repeated for the entire list.

 

[Eric W]

Here is my test sheet:

Dynamic functions.xlsm
	A	B	C	D	E	F	G	H	I
1	List	Code	Amt			Matching list		x	10
2	a	x	11			a
3	b		12			c
4	c	x	13			d
5	d	x	14
6	e		15
7	f		16
8	a	x	17
9	b		18
10	c		19
11
Sheet4

Cell Formulas
Range	Formula
F2:F8	F2	=IFERROR(INDEX($A$2:$A$26,AGGREGATE(15,6,(ROW($A$2:$A$26)-ROW($A$2)+1)/($B$2:$B$26=$H$1)/($C$2:$C$26>=$I$1)/(COUNTIF($F$1:$F1,$A$2:$A$26)=0),1)),"")

If you're getting the same value repeated, then that's an issue with the COUNTIF function. Notice the $F$1:$F1 in the formula. There is no $ in front of the second 1, so as you drag the formula down, the range gets larger. So the COUNTIF looks at all the values above the current row to see if the value has been used before.

 

[excelbytes]

Your formula works, but it wasn't the issue concerning the mixed cell reference in the COUNTIF function. It was that my results column is E and your formula referenced column F. So when I made that change, it worked. Thanks!

 

[Eric W]

There you go! I knew it had to be something small. Glad you got it working! Thanks for the feedback!