Thread: Finding / searching multiple values in one cell

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!

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.