Hi all,

I have a list of 1000 items in Row A. I have a list of key words in row
C see below

C1 Dog
C2 Cat
C3 Mouse
C4 Horse

In row 2 I want a formula to return Dog if it contains the word Dog
with a list of text

e.g if A2 text = "My Dog " then B2 would return "Dog".
Note Sometimes a cell text will have "My Cat and Dog" so would want to
return C1 & C2 value.
If text not in my list it should return "".

Thanks in advance

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=554466

Mmmm, looks like a challenge!. Just a couple of comments/questions.

I assume you mean columns, not rows.

How do you want the data displaye in the case "my cat and dog"?

"cat, dog" in 1 cell?

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=554466

Indeed I did mean column.

"My Cat and Dog" then says "Cat, Dog" in cell 1

I can found one by using search IF(SEARCH($F$1,A1,1)=1,$F$1,"") but if
I add a extra & if
=IF(SEARCH($F$1,A1,1)=1,$F$1,"")&IF(SEARCH($F$2,A 1,1)=1,$F$2,"") I
get a Value# error


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=554466

I have written a custom function for you that I think does what you
want

Function ListSearch(Target As Range, SearchList As Range)
Dim myTarget, mySearchList As Range
Set myTarget = Target
Set mySearchList = SearchList

For Each cell In mySearchList
On Error Resume Next
x = Application.WorksheetFunction.Find(cell.Value,
myTarget.Value, 1)
If x 0 Then
If myAnswer < 0 Then myAnswer = myAnswer & ", "
myAnswer = myAnswer & cell.Value
x = 0
End If
Next cell
If myAnswer = 0 Then myAnswer = ""
ListSearch = myAnswer
End Function

Target = the cell with the "my cat and dog" mySearchRange is the list
of possible matches


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=554466

Thanks Mallycat

Works a treat

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=554466

Mallycat,

Now do I amend the code to deal with different cases. e.g Some text
proper case and some in upper case


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=554466