# 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?

Tuesday, April 14, 2015 6:20 AM

• 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 ExplicitFunction 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 Friday, April 17, 2015 6:09 AM
Tuesday, April 14, 2015 7:15 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 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 Friday, April 17, 2015 6:08 AM
Wednesday, April 15, 2015 10:11 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 ExplicitFunction 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 Friday, April 17, 2015 6:09 AM
Tuesday, April 14, 2015 7:15 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.

Wednesday, April 15, 2015 2:19 AM
• 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.

Wednesday, April 15, 2015 6:44 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 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 Friday, April 17, 2015 6:08 AM
Wednesday, April 15, 2015 10:11 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!

Thursday, April 16, 2015 5:43 AM
• 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.
• 