Finding / searching multiple values in one cell

**fullysic** · 02-01-2012, 02:06 AM

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** · 02-01-2012, 02:28 AM

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

Please Login or Register  to view this content.

Hope it help

Regards

**fullysic** · 02-01-2012, 02:29 AM

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** · 02-01-2012, 02:42 AM

Hi,

thanks for ypur kind feedback.

Regards

**pike** · 02-01-2012, 02:45 AM

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