# Search a cell for an array or words/phrases and return a value

#### mfulche1

##### New Member
Hoping with some help on an issue I have encountered at work. I'm an expert in pivot tables and decent with vlookup, but in need of help to solve the following. Have tried using VLOOKUP and SEARCH functions here, but didn't have luck using that given the need for a list/array and non-exact matches. The real spreadsheet is thousands of lines long and much more detailed, so I have created a basic table here of what I am trying to accomplish:
 Search Terms Column to Search Return Value girl The boy went to the park cat The girl wen to the park Yes zoo The dog played ball at the park pet shop The cat ate food at the zoo Yes The zoo was closed Yes The dog was not at the park The boy went to the pet shop Yes

<tbody>
</tbody>

How can I automatically return the value in column C? I don't care if it says 'yes' or 'true' or '1'...just that it indicates in each row if the cell in column b has one of the search terms identified in column A rows 1-5. Any help you could provide would be greatly appreciated! Thank you in advance

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### VBA Geek

##### MrExcel MVP
try in C2

=LOOKUP(2,1/COUNTIF(B2,"*"&\$A\$2:\$A\$5&"*"))

#### mfulche1

##### New Member
Wow - You make it look so easy! That worked like a charm. Now I am going to dig in and figure out what the heck you did so I can use it more in the future THANK YOU SO MUCH! Appreciate you pointing me to this solution.

Replies
5
Views
544
Replies
1
Views
801
Replies
1
Views
824
Replies
4
Views
4K
Replies
1
Views
2K

1,195,666
Messages
6,011,048
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back