Find the words automatically please help!!

[Anıl Adaş]

Hi,

I have words in the excel sheet attached and I need to find the rows including these words in a different excel sheet. 

At the pictures below, I chose a random word(BARSAN). For example, it's BARSAN in first sheet, I want to find automatically the rows including BARSAN GLOBAL(or sth like that) in second sheet. I can't do it simply with ctrl+f method because there are lots of rows and words in my real excel file. If anybody knows a way makes it possible with a macro or formula please tell me.. 

[#2]

Hi Anil,

That could be array formula

=IFNA(MATCH(1,INDEX(SEARCH(A1,Sheet2!$A$1:$A$10),0,0),0),"")

[#3]

Sergei first thanks,

Is it possible to see the original word(such as C column) in sheet2 instead of its row number?

 

[#4 · Accepted solution]

Anil, when it'll be

=IFNA(INDEX(Sheet2!$A$1:$A$10,MATCH(1,INDEX(SEARCH(A1,Sheet2!$A$1:$A$10),0,0),0)),"")

(regular formula)