Oct 18 2018 12:54 AM
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..
Oct 18 2018 05:43 AM
Hi Anil,
That could be array formula
=IFNA(MATCH(1,INDEX(SEARCH(A1,Sheet2!$A$1:$A$10),0,0),0),"")
Oct 18 2018 06:31 AM
Sergei first thanks,
Is it possible to see the original word(such as C column) in sheet2 instead of its row number?
Oct 18 2018 07:17 AM
SolutionAnil, 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)
Oct 18 2018 07:17 AM
SolutionAnil, 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)