Any formula to get the desired result

[rush2rajen]

Hi Excel Masters,

I had attached a file.
Is it possible to get the desired result by Vlookup, Index-Match, Vlookup-index-match or any formula ?



I would appreciate the help.
Thanks in advance

Rush

 

[Naresh Yadav]

pls try this one..

=IF(COUNTIF($B$2:$B$46,$N5)=1,INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),1)),IF(COUNTIF($B$2:$B$46,$N5)=2,INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),1))&"-"&INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),2)),IF(COUNTIF($B$2:$B$46,$N5)=3,INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),1))&"-"&INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),2))&"-"&INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),3)),IF(COUNTIF($B$2:$B$46,$N5)=4,INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),1))&"-"&INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),2))&"-"&INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),3))&"-"&INDEX($A$2:$A$46,SMALL(IF($B$2:$B$46=$N5,ROW($B$2:$B$46)-1),4)),""))))