Search using range for multiple search criteria

[ScottKar]

Hi,
I'm having trouble getting the SEARCH function to work using a range for multiple search criteria. I think my problem is getting the array to work.

I have a range called Animals consisting of the following 4 rows

dog

cat

fish

cow

<tbody>
</tbody>

Column A has many rows of text. For example, A2 = "I have a dream" and A3 = "I want a dog and a unicorn"

I tried using this formula
=SEARCH(Animals,A2)
I typed the search in the formula box and hit CTL-SHIFT-RETURN to enter it in as an array so that it looked like this {=SEARCH(Animals,A2)}

But I got the #VALUE! for every cell that contains "cat", "fish" or "cow". The only time the formula worked is when the cell contained "dog". This tells me the array isn't working.

I would like to try =SEARCH({Animals},A2) but I don't know how to enter an array inside a formula.

I'm sure this is an easy question for some smart cookie but I'm stumped.

Thank you,
ScottKkar

 

[My Aswer Is This]

Explain what your end result is. Are you searching a certain range for certain words? If so what range are you searching in and what are the values your searching for? And what do you want to do when you do find the word?

 

[ScottKar]

Hi BR,

Thanks for looking at my post.

Here's the (kinda) totality of what I'm trying to do:

Column A contains many cells that have text

A2 - "I have a dream"
A3 - "I want a dog and a unicorn"
A4 - "I like cats more than dogs"
A5 - "I like fishing"
A6 - "Pigs annoy me"


I want Column B to show which animals, if any, from range: Animals are in Column A. And, I'd want the animal name to show up.


So, for the example above I want Column B to show
B2 - [blank]
B3 - dog
B4 - cats dogs
A5 - fishing
A6 - [blank]

I know I'll be using =IFERROR to eliminate the #VALUE! and
=MID to extract the text

But, I cannot figure out how to write the base formula (i.e. the formula that determines if any of the values in range: Animals exist in the cell in Column A).


To answer your other questions:

Are you searching a certain range for certain words? Yes, I'm creating a range that has the words I wish to find in the cells with text.

what range are you searching in and what are the values your searching for? In my example I used a range called Animals but my real life range will contain:

Not produced
Not poduced
refused
refuse
* plus about 4 other variants b/c of misspellings in the text cells.​

Thank you,
Scott