Compare two different spreadsheets to find common string text ?

[Maverick27]

Attn Excel Gurus

I have 2 separate worksheets files & I want to find common string text.

I want to find the corresponding 'SKU Code' (Col A ) for the matching 'Product Code ' (Col C) & result should be output to the 2nd excel worksheet.

Kindly refer to screenshots below for an example.

Data Source Excel worksheet

Screenshot

Captured with Lightshot

prnt.sc

Find & Match Excel worksheet
Screenshot

Anyone care to share the Excel function to achieve this ?

Thanks

 

[DanteAmor]

Try this:

Libro1
	A	B	C	D
1	Item	Stock	Coll	Output
2	AAQBLM23CK	107	AURA	BNP0014
3	AAQBLM25CK	246	AURA	BNP0015
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=INDEX(Sheet2!$A$1:$A$1000,SUMPRODUCT((ISNUMBER(SEARCH(A2,Sheet2!C$1:C$1000)))*ROW(C$1:C$1000)))

Note: Change Sheet2 to the name of the sheet where you have the product names

 

[Maverick27]

=INDEX(Sheet2!$A$1:$A$1000,SUMPRODUCT((ISNUMBER(SEARCH(A2,Sheet2!C$1:C$1000)))*ROW(C$1:C$1000)))

Thank you for taking the initiative to reply.

The function works fine.