Vlookup (or alternative) using "contains" condition

[jaldhi_s]

Hi- I am a excel newbie so please bare with me if this is a basic question. I have a table like this below. My table has hundreds of rows, i am just simplifying here with 5 rows.

If i want to find out which of the boxes contains Apple, how do i do this. So in example below, answer should be Box 1, 3 and 5.

thanks a lot for your help

Box number	Content
1​	Orange, banana, apple
2​	Banana
3​	Apple, Orange
4​	Banana, Orange
5​	Apple

 

[jasonb75]

You can do it with TEXTJOIN

Book1
	A	B
1	Box number	Content
2	1	Orange, banana, apple
3	2	Banana
4	3	Apple, Orange
5	4	Banana, Orange
6	5	Apple
7
8		Box 1, 3, 5
Sheet11

Cell Formulas
Range	Formula
B8	B8	="Box "&TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH("Apple",B2:B6)),A2:A6,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Cross posted lookup (or alternative) using "contains" condition

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[jaldhi_s]

Cross posted lookup (or alternative) using "contains" condition

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Thanks for poinring that out. I didn't know about the rule. Apologies.

 

[CA_Punit]

What version of excel do you use.
Does post 2 solve your probelm????

 

[jaldhi_s]

What version of excel do you use.
Does post 2 solve your probelm????

I am using office 365...i can't get to a computer now but will try the post 2 suggestion as soon as I can. Thanks.

 

[Fluff]

Another option if you have the dynamic functions

+Fluff New.xlsm
	A	B	C	D
1	Box number	Content		Apple
2	1	Orange, banana, apple		Box 1, 3, 5
3	2	Banana
4	3	Apple, Orange
5	4	Banana, Orange
6	5	Apple
Main

Cell Formulas
Range	Formula
D2	D2	="Box "&TEXTJOIN(", ",1,FILTER(A2:A6,ISNUMBER(SEARCH(D1,B2:B6))))

 

[jaldhi_s]

Thanks Fluff. I don't know what is dynamic formulas and whether I have it or not, but I should be ablw to get to a computer today and try it out.
Will report back once I have tried it.

 

[jaldhi_s]

Thanks Fluff and Jasonb. The formula worked. It saved me a lot of time so genuine thanks. I need to use this forum more..

 

[Fluff]

Glad we could help & thanks for the feedback.