Finding second and third matches of a cell within column

[DrAGen]

I have a vertical list consisting bunch of names. I'm looking for a way to find the second and third matches of a cell value prepended with a space.


I'm using =VLOOKUP("*"&" " &B2&"*",A:A,1,FALSE) for the match1 below, which is working fine, but I couldn't figure out a way to automatically find second and third matches.

Any help would be greatly appreciated.

[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]Last[/TD]
[TD]Match1[/TD]
[TD]Match2[/TD]
[TD]Match3[/TD]
[/TR]
[TR]
[TD]Samantha Jones

[/TD]
[TD]Jones[/TD]
[TD]Samantha Jones[/TD]
[TD]Frank Jones[/TD]
[TD]Mark Jones III[/TD]
[/TR]
[TR]
[TD]Frank Jones
[TABLE="width: 500"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim Fitzgerald
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jones, Tim
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joseph Jonsten
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nicole-Jones
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mark Jones III[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike Brown
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try this array formula in C2 copied across
=IFERROR(INDEX($A:$A,SMALL(IF(ISNUMBER(SEARCH(" "&$B2,$A$2:$A$100)),ROW($A$2:$A$100)),COLUMNS($C2:C2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[DrAGen]

Hi Marcelo Branco - that worked Thank you so much for your help. I really do appreciate it.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.