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.

NAME	Last	Match1	Match2	Match3
Samantha Jones	Jones	Samantha Jones	Frank Jones	Mark Jones III
Frank Jones <tbody> </tbody>
Tim Fitzgerald
Jones, Tim
Joseph Jonsten
Nicole-Jones
Mark Jones III
Mike Brown

<tbody>
</tbody>

 

[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.