Finding / searching multiple values in one cell

[fullysic]

Hey guys,

For the purpose of this question I'm going to use animals as an example. I have a list of animals (Range: D2:D5) and I want to see if any of the animals in that list appear in a specific cell (Range: B2). The cell is formatted the following way:

Animal name / animal name / animal name etc

If an animal is found in the above cell then I would like to return a 1 next to it.

I've tried to do this using the following array formula, but am having no luck:

{=IF(FIND(D2:D5,B2,1)>1,1,0)}

Could anyone please help me out as to where I'm going wrong? I have attached a sample sheet with how my results would look if it will help you get the picture.

Thanks!

[canapone]

Hi, array formula, but (your example) "ant" is part of elephant and it's tricky.

=--ISNUMBER(MATCH(1,SEARCH("*"&$D$2:$D$5&"*",B2)))

Hope it help

Regards

[fullysic]

Thank you for your reply. Maybe I chose the wrong examples - for my actual data it won't have the problem you specified above as they are all three digit numbers. Does this make a difference to the formula you posted?

Edit: Have just tried and that works great. Thanks very much Canapone.

[canapone]

Hi,

thanks for ypur kind feedback.

Regards

[pike]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save