Comparing two rows in Excel for a particular word and returning the content found in that cell where the word is in a new row

[Sailadarohit]

I want to compare two rows in Excel for a particular word and return the entire content found in that cell where the word is present in the new rows.
So i am searching with the word nike in the two rows, the formula should search both the rows for the word nike and if found return the entire content found along with the word nike in the new rows else return NULL.
Row A and B are my inputs, output should look like row C and D
Please refer the attachment for more information.

 

[Sailadarohit]

No rules change peter i want to be cautious this time as these use cases are coming soon. I don't want to bother you for the same thing again and again that's why being proactive while you are already working on that. As i type this i get one such request

 

[Peter_SSs]

No rules change

If there is no rule change and the last suggestion ..

worked very well

.. why are we continuing?

Reminder, I cannot copy from an image.

 

[Sailadarohit]

Yes peter I am working on fix so that i can share the snippet from XL2BB. My new question whenever i post again it will have the snippet from XL2BB

For the rules i meant the rules related to the results that is it should return the entire string in which the word is present
I have highlighted the 3 use cases which needs to be addressed.
Sorry for addition of new use cases. I can promise these are the last additions.
Thanks in advance!!

 

[Peter_SSs]

.. these are the last additions.

Hmm, that sounds familiar
(from post #14)

.. there is a last case which needs to be addressed, ..

 

[Sailadarohit]

No peter this is the last one all things addressed

 

[Sailadarohit]

Book1
	A	B	C	D
1	Actual	Assumed	WordPresent Actual	WordPresent Assumed
2	Dress, brand_nike_costly_new, Store	Jeans, brand_nike_air_cheap_Used, Online	brand_nike_costly_new	brand_nike_air_cheap_Used
3	Online, brand_puma_shirt, Paid	Store, brand_puma_jeans, Return	NULL	NULL
4	Offline, brand_nike_jeans, Card	Shirt, brand_nike_tshirts	brand_nike_jeans	brand_nike_tshirts
5	Dress, brand_moniker_costly_new, Store	Jeans, brand_moniker_costly_new	NULL	NULL
6	black_nike_pro	black_nike_beta	black_nike_pro	black_nike_beta
7	online,black_nike_pro	black_nike_air	black_nike_pro	black_nike_air
8	offline,red_nike,pants	shop,red_nike_air,shirts	red_nike	red_nike_air
9	Dress, Hyderabad,brand_nike_costly_new, Store	Jeans, Hyd, brand_nike_air_cheap_Used, Online	brand_nike_costly_new	brand_nike_air_cheap_Used
10	Online, Banglore, Return, brand_puma_shirt, Paid	Store, Chennai, brand_puma_jeans, Return	brand_puma_shirt	brand_puma_jeans
11	online,Jeans, Hyderabad,black_nike_pro	online,Hyderabad,black_nike,shirt	black_nike_pro	black_nike
Sheet1

 

[Sailadarohit]

Here we go peter fixed the issue of mini sheet

 

[Peter_SSs]

Thanks. Your profile lists 4 versions of Excel. Which one(s) does the formula have to work in? It may be that there are 'better' solutions that work in the later versions.

Why isn't the expected result in C10 and D10 "NULL" since "_nike" does not appear in A10 or B10?

 

[Sailadarohit]

My bad peter in hurry i wrote the brand name wrong.. please read them as nike

 

[Peter_SSs]

What is the answer to my first question in post 28?