Vlookup & search function help

  • Thread starter Thread starter VBA Noob
  • Start date Start date
V

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
 
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
 
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
 
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,

Now do I amend the code to deal with different cases. e.g Some text
proper case and some in upper case
 
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
 
Back
Top