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>
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,918
Members
414,416
Latest member
Nobu

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