Index match to find the results for the 2nd duplicate value

[SharmaAntriksh]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Antriksh[/TD]
[/TR]
[TR]
[TD]David[/TD]
[/TR]
[TR]
[TD]David[/TD]
[/TR]
[TR]
[TD]Antriksh[/TD]
[/TR]
</tbody>[/TABLE]

how do i use index and match to find the results for the duplicate values? i am getting duplicate result for the second "Antriksh". let me know if this is possible.

 

[wideboydixon]

Your question is a little light on detail. However, something like this perhaps?

Book1
	A	B	C	D	E
1	Antriksh	a1		Antriksh	a1
2	David	d1		David	d1
3	David	d2		Antriksh	a2
4	Antriksh	a2		David	d2
Sheet1

Cell Formulas
Range	Formula
B1	{=INDEX($E$1:$E$4,SMALL(IF($D$1:$D$4=$A1,ROW($D$1:$D$4)-ROW($D$1)+1),COUNTIF($A$1:$A1,$A1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[SharmaAntriksh]

Thanks a tonne! 1 question for the countif part, i have seen a lot of people using formula incrementer such as ROWS() being used in their but it doesn't work in this case, do you know why?

 

[wideboydixon]

It doesn't work in this case because you're not using a single lookup value.

WBD