Search for a substring in next column

[i_LKarthick]

Hi,

good day!.

I have Column A - which has paragraph about product. Similarly I have Column B which contain many rows.

Need to check is there any string Match in Column B.if any substring match then I have to pull "Serial No" Value to output sheet.

Source:

	Serial No	Column A
	1	Product1 is available, Product 2 is also available.

Serial No	Column B
1	Product available are Product1,Product2,product3
2	Base Product is Product5
3	Product1 also available
4	Product2

Expected Output:

Column A	Output(Serial No)
Product1 is available, Product 2 is also available.	1
Product1 is available, Product 2 is also available.	3
Product1 is available, Product 2 is also available.	4

[Amol_Telore · Accepted solution]

Hey @i_LKarthick 

Here is my solution. Hope it solves your concern.

[i_LKarthick]

Hi,

Thanks for the solution. it work fine for me. Now I got one more new scenario,

I have Column A - which has paragraph about product.  In Column B have to check is there any string Match .if any substring match then I have to pull "Serial No" Value to output sheet.

Source:

	Serial No	Column A
	1	Product1 is available, Product 2 is also available,Pro,123,ABC.

Serial No	Column B
1	Product available are Product1,Product2,product3
2	Base Product is Product5
3	123
4	Pro
3	Product1 also available
4	Product2

Expected Output:

Column A	Output(Serial No)
Product1 is available, Product 2 is also available.	1
Product1 is available, Product 2 is also available.	3
Product1 is available, Product 2 is also available.	4