# Search Help

#### CRichards

##### New Member
I am trying to create a formula in cell D2 (Cat) that does a lookup on C2 and sees if it contains a word on the list range of A2-A5. If it does contain the word, then it will equal the word it contains on the list. D2 would say "Cat" and D3 would say "Dog".
 A B C D 1 LIST DESCRIPTION 2 Dog Black_Cat_Theo Cat 3 Cat There was a dog named Sparky Dog 4 Turtle 5 Hamster

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### JoeMo

##### MrExcel MVP
Here's a UDF you can try. After you install it, invoke it just as you would a worksheet function (see example below). Copy D2 down.
Excel Workbook
ABCD
1LIST*DESCRIPTION*
2Dog*Black_Cat_TheoCat
3Cat*There was a dog named SparkyDog
4Turtle***
5Hamster***
Sheet3

Code:
``````Function Animal(S As String, Lkup As Range) As String
Dim V As Variant
V = Lkup.Value
For i = 1 To UBound(V, 1)
If InStr(UCase(S), UCase(V(i, 1))) > 0 Then
If Animal = "" Then
Animal = V(i, 1)
Else
Animal = Animal & ", " & V(i, 1)
End If
End If
Next i
End Function``````

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,682
Messages
5,838,784
Members
430,568
Latest member
bortey

### 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