Find value return left column for a large list

[behedwin]

Hi

I need help to solve a problem.

I have a excelfile with about 3000 rows.
In column C i have Names
In column H i have a string of text containing key words i want to find an associate to column C

For example
Row 1 - NAME: Oskar | STRING: text i want to search for

I want a formula that look for my search word
Returns each row that have the word im lookng for and prints the name of that row in a new list.

 

[Toadstool]

Hi BEHEdwin,

Is this what you wanted?

BEHEdwin.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Row 1	When	Name					String			Row	Name
2	Row 2		Oskar					What is searched			3	Charles
3	Row 3		Charles					When I want to search			5	John
4	Row 4		Sarah					How can I W H E N			6	Bradley
5	Row 5		John					You will do it when?			8	Bert
6	Row 6		Bradley					You will do it when			9	Susan
7	Row 7		Vanessa					OK what
8	Row 8		Bert					You will do it when
9	Row 9		Susan					It is when I want
10	Row 10
Sheet1

Cell Formulas
Range	Formula
K2:K10	K2	=IFERROR(AGGREGATE(15,6,ROW($A$2:$A$3000)/(SEARCH($B$1,$H$2:$H$3000)>0),ROW()-ROW($K$1)),"")
L2:L10	L2	=IF(K2="","",INDEX(C:C,K2))