Answered by:
Searching List of items in a larger string, return matching value
Question

Hi everyone,
Hoping you can help. I've tried a bunch of things but I can't seem to figure this out. Any help is appreciated!
Basically, I have a list of items, I want to search the word in a1 in another list of longer strings, if its somewhere in there, return column B beside the original list.
Example:
Column A
Dog
Cat
Rabbit
Column B
D1
C1
R1
Column C
Dog went to town
So from Column A, search for all of those words in column C, if it finds a word (which it should find Dog), then return the corresponding value in column B.
Also can this be done regardless of upper case/lower case?
Does this all make sense?
Answers

So from Column A, search for all of those words in column C, if it finds a word (which it should find Dog), then return the corresponding value in column B.
Copy the code below into a regular module, then add this formula:
=MatchWordValues(A1:A3,B1:B3,C1)
Andreas.
Option Explicit
Function MatchWordValues(ByVal Words As Range, ByVal Values As Range, _
ByVal Where As Range) As Variant
'The function assumes that Words and Values have the same size
Dim W As Range, R As Range
Dim What As Variant
Dim i As Integer, vr As Long, vc As Long
'For each word
For Each W In Words
'There are 3 cases
For i = 1 To 3
'Surround the word with a search mask
Select Case i
Case 1: What = "* " & W & " *"
Case 2: What = "* " & W
Case 3: What = W & " *"
End Select
'Search it in Where
Set R = Where.Find(What, LookIn:=xlValues, LookAt:=xlWhole)
If Not R Is Nothing Then
'Calculate the row, column
vr = W.Row  Words.Row + 1
vc = W.Column  Words.Column + 1
'Return the corresponding result
MatchWordValues = Values.Cells(vr, vc)
Exit Function
End If
Next
Next
'If we get here, no word is found, return #NA
MatchWordValues = CVErr(xlErrNA)
End Function
 Marked as answer by Justin1426 Friday, April 17, 2015 6:09 AM

It seems so simple.
Hi Justin,
the best solutions are always simple... if you know them. :) And if you ask 10 programmers how to solve your problem, you get 10 different answers.
When you ask a question in a forum, you have also to consider the view "from the other side":
I don't know your skills.
I don't know which kind of macro you want (there are several!).
I don't know the real data, you just provide a sample.
I don't know all the other circumstances (e.g. merged cells)
An example based on the answer from Starain:
Sub Test()
Dim R As Range
Dim Data, i As Long
Data = Split(Range("C1"))
For Each R In Range("A1:A3")
For i = 0 To UBound(Data)
If Data(i) = R Then
Range("C2") = R.Offset(, 1)
Exit Sub
End If
Next
Next
End Sub
That works, not question, but are you able to adapt that for your real needs? I do not think so.So, how simple do you want it? ;)
Andreas.
 Marked as answer by Justin1426 Friday, April 17, 2015 6:08 AM
All replies

So from Column A, search for all of those words in column C, if it finds a word (which it should find Dog), then return the corresponding value in column B.
Copy the code below into a regular module, then add this formula:
=MatchWordValues(A1:A3,B1:B3,C1)
Andreas.
Option Explicit
Function MatchWordValues(ByVal Words As Range, ByVal Values As Range, _
ByVal Where As Range) As Variant
'The function assumes that Words and Values have the same size
Dim W As Range, R As Range
Dim What As Variant
Dim i As Integer, vr As Long, vc As Long
'For each word
For Each W In Words
'There are 3 cases
For i = 1 To 3
'Surround the word with a search mask
Select Case i
Case 1: What = "* " & W & " *"
Case 2: What = "* " & W
Case 3: What = W & " *"
End Select
'Search it in Where
Set R = Where.Find(What, LookIn:=xlValues, LookAt:=xlWhole)
If Not R Is Nothing Then
'Calculate the row, column
vr = W.Row  Words.Row + 1
vc = W.Column  Words.Column + 1
'Return the corresponding result
MatchWordValues = Values.Cells(vr, vc)
Exit Function
End If
Next
Next
'If we get here, no word is found, return #NA
MatchWordValues = CVErr(xlErrNA)
End Function
 Marked as answer by Justin1426 Friday, April 17, 2015 6:09 AM

Wow thank you very much. I was just wondering, is there no way to do this in a single cell?
It seems so simple. Search for the words from a list in a column (ie Dog, cat, rabbit, etc), in another cell (ie "Dog in the backyard", and return the value beside the word it finds. In this case Dog, so return D1 for example.

Hi Justin,
>> Search for the words from a list in a column (ie Dog, cat, rabbit, etc), in another cell (ie "Dog in the backyard"
You need to split the word (e.g. Dog in the backyard) into an array through split method, then iterate the array and find each word in the range.
This way is achieved through Range.Find method too.
Regards
Starain
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey. 
It seems so simple.
Hi Justin,
the best solutions are always simple... if you know them. :) And if you ask 10 programmers how to solve your problem, you get 10 different answers.
When you ask a question in a forum, you have also to consider the view "from the other side":
I don't know your skills.
I don't know which kind of macro you want (there are several!).
I don't know the real data, you just provide a sample.
I don't know all the other circumstances (e.g. merged cells)
An example based on the answer from Starain:
Sub Test()
Dim R As Range
Dim Data, i As Long
Data = Split(Range("C1"))
For Each R In Range("A1:A3")
For i = 0 To UBound(Data)
If Data(i) = R Then
Range("C2") = R.Offset(, 1)
Exit Sub
End If
Next
Next
End Sub
That works, not question, but are you able to adapt that for your real needs? I do not think so.So, how simple do you want it? ;)
Andreas.
 Marked as answer by Justin1426 Friday, April 17, 2015 6:08 AM

Thank you very much to both of you! I was actually able to get this to work. :)
And yes I was able to modify this to work! :)
I think I was hoping it could be done in a single cell formula without a macro, but I can definitely work with this if not.
Thank you again for all of your help!

Hi Justin,
>> I was hoping it could be done in a single cell formula without a macro
For this requirement, the Excel IT Pro Discussion forum is a better place for this question.
Regards
Starain
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey. 