Snasui Excel Forum

Hi Dear,

I need your input for the lookup value return many unique values down.
I use array formula index with small but it is not working well.
I attached a sample with the result needed

Best Wishes

May be,

E2 =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$17)/(FREQUENCY(MATCH($A$2:$A$17&$B$2:$B$17,$A$2:$A$17&$B$2:$B$17,0),ROW($B$2:$B$17)-ROW($B$2)+1)>0)/ISNUMBER(SEARCH($D$2&"*",$B$2:$B$17))/($C$2=$A$2:$A$17),ROWS(E$1:E1))),"")

Confirm with enter and copy down.

..

E2

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($2:$17)/($A$2:$A$17&LEFT($B$2:$B$17,SEARCH("-",$B$2:$B$17)-1)=$C$2&$D$2)/(MATCH($A$2:$A$17&$B$2:$B$17,$A$2:$A$17&$B$2:$B$17,0)=ROW($1:$17)),ROW(1:1))),"")

Norkaz

logic wrote: Thu May 20, 2021 11:01 am May be,

E2 =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$17)/(FREQUENCY(MATCH($A$2:$A$17&$B$2:$B$17,$A$2:$A$17&$B$2:$B$17,0),ROW($B$2:$B$17)-ROW($B$2)+1)>0)/ISNUMBER(SEARCH($D$2&"*",$B$2:$B$17))/($C$2=$A$2:$A$17),ROWS(E$1:E1))),"")

Confirm with enter and copy down.

Norkaz

..

Hi guy,

As I did double check I was sure my previous formula was wrong , please ignore my previous suggestion, then please use the formula below or could be possible to use the method that " logic" provided.

E2

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($2:$17)/($A$2:$A$17&LEFT($B$2:$B$17,SEARCH("-",$B$2:$B$17)-1)=$C$2&LEFT($D$2,SEARCH("-",$D$2)-1)/(MATCH($A$2:$A$17&$B$2:$B$17,$A$2:$A$17&$B$2:$B$17,0)=ROW($1:$17))),ROW(1:1))),"")


Apologies for any inconvenience.

** ขอบคุณ คุณ logic ที่มาช่วย ครับ

Norkaz
Chiangmai / Thailand
20-May-2021

Thank it works