Search Help

[CRichards]

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>

 

[JoeMo]

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
	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	*	*	*
Sheet3

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