Retreiving Nth Results from an Index Match Using Wildcard Search Term

[kyleno]

I am building somewhat of a rudimentary search engine that looks for a name, address or unique identifier, using an Index Match formula that searches with a wild card:

=INDEX(Name,MATCH("*"&F3&"*",Name,0))

"Name": is the defined name for column C on a separate sheet.
F3: is "search field"

The current formula (above), only returns the first match. The source data is only 927 lines so not overly large. I would like to have an additional page that will show the potential results from the wildcard search via a formula. Therefore needing results; 2, 3, 4, 5 etc. from the Index Match. I have searched high and low for solutions but I have found none in regards to retrieving </SPAN></SPAN>multiple results from a wildcard search, only on a specific term.

It seems that there should be a simple solution to this, but I am unable to find anything!

Please and thank you.

~ Ky

 

[Aladin Akyurek]

F3: search value

F4: Result

F5, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Name,SMALL(IF(ISNUMBER(SEARCH(F$3,Name)),ROW(Name)-ROW(INDEX(Name,1,1))+1),ROWS(F$5:F5))),"")

 

[kyleno]

That worked brilliantly! Thank you so much

 

[Aladin Akyurek]

That worked brilliantly! Thank you so much

You are welcome. Thanks for providing feedback.