Vlookup & search function help

[VBA Noob]

Hi all,

I have a list of 1000 items in Row A. I have a list of key words in ro
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 Do
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 t
return C1 & C2 value.
If text not in my list it should return "".

Thanks in advance

VBA Noo

 

[Mallycat]

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

 

[VBA Noob]

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,A1,1)>=1,$F$2,"") I
get a Value# error

 

[Mallycat]

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

 

[VBA Noob]

Thanks Mallycat

Works a treat

VBA Noob

 

[VBA Noob]

Mallycat,

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

 

[Mallycat]

The easiest way to handle case when doing text searches is to conver
both sides of the search test to the same case

ie change the line as follows

x = Application.WorksheetFunction.Find(UCase(cell.Value)
UCase(myTarget.Value), 1)

Regards

Mat