Search Help

CRichards

New Member
Joined
Nov 24, 2014
Messages
4
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".
ABCD
1LIST DESCRIPTION
2Dog Black_Cat_TheoCat
3Cat There was a dog named SparkyDog
4Turtle
5Hamster

<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
Joined
May 26, 2009
Messages
17,916
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top