Index Match - Return Small Unique Values

[hrayani]

Hello Friends,

I am using this formula to get smallest value... but I need to get the smallest unique value if somebody can help

Required answer in cell A10 is 126

Please have a loot below...

Book2
	A	B
1	REF #	NAME
2	125	Alpha
3	125	Alpha
4	125	Alpha
5	126	Alpha
6
7
8	REF #	NAME
9	125	Alpha
10	125	Alpha
11
12
13	126 requierd in cell A10 coz the 2nd
14	smallest unique value is 126
Sheet1

Cell Formulas
Range	Formula
A9:A10	A9	=INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5=B9,ROW($B$2:$B$5)-ROW(INDEX($B$2:$B$5,1,1))+1),ROWS($A$1:$A1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Regards,

Humayun

 

[Fluff]

Maybe

+Fluff New.xlsm
	A	B
1	REF #	NAME
2	125	Alpha
3	125	Alpha
4	123	Alpha
5	126	Alpha
6
7
8	REF #	NAME
9	125	Alpha
10	123	Alpha
11	126	Alpha
List

Cell Formulas
Range	Formula
A9:A11	A9	=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-ROW($A$2)+1)/(($B$2:$B$5=$B9)*ISNA(MATCH($A$2:$A$5,$A$8:$A8,0))),1)),"")

 

[hrayani]

Thanks for the reply Fluff,

But its giving 1st, 2nd & 3rd Occurrence whereas, I need the 1st smallest unique value then 2nd smallest unique value and so on

It should return 123, 125 & 126 in range A9:A11

 

[Fluff]

Ok, how about
=IFERROR(AGGREGATE(15,6,$A$2:$A$5/(($B$2:$B$5=$B9)*ISNA(MATCH($A$2:$A$5,$A$8:$A8,0))),1),"")

 

[hrayani]

Thanks Fluff.... now giving the desired result

 

[Fluff]

You're welcome & thanks for the feedback.