Compare sentense between 2 excel files and find match cell.

Hi, I have a list of books (TEST.XLS) which I need to compare them with (Copy of TEST.XLS) to find is is already available or not.

and those FOUNDED result from (TEST) will be copy to the Column B (Copy of TEST).

For example List A2:TEST found match with cell C2:Copy of TEST, I want C2:Copy of TEST cell value copy to B2:TEST

test.png

Thank you in advance

http://www.excelforum.com/attachment...1&d=1461313772

http://www.excelforum.com/attachment...1&d=1461313772

Formula in B2:

=IFERROR(INDEX([TEST.xlsx]Sheet1!C:C,MATCH("*"&A2&"*",[TEST.xlsx]Sheet1!C:C,0),),"")

then copy down...

Hi Jindon,

Thank you for the formula. works great!

btw, can combine 1 formula to scan column C and column G together?

and, possible to insert this formula into vba/macro for easier solution without dragging all the way down? because i have 20000 + rows!

Assuming both workbooks are in the same folder.
To Copy of test.xlsx

Please Login or Register  to view this content.

HI jindon,

Thanks again for your help. Code given works like charm!

I want to ask how do you convert those formula into vba formula? Im thinkin to use =(formula)&""&formula to combine both formula so that both results will be on 1 column. Could you help me to convert into vba formula?

GOT IT RIGHT! THANKS!

Please Login or Register  to view this content.