Vlookup & search function help
 

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

Vlookup & search function help
 

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

Vlookup & search function help
 

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

Vlookup & search function help
 

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

Vlookup & search function help
 

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

Vlookup & search function help
 

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

Vlookup & search function help
 

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

ie change the line as follows

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

Regards

Matt


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