none
Searching List of items in a larger string, return matching value RRS feed

  • 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

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
    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 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
    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 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
    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.
    Click HERE to participate the survey.

    Wednesday, April 15, 2015 6:44 AM
    Moderator
  • 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
    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.
    Click HERE to participate the survey.

    Thursday, April 16, 2015 5:58 AM
    Moderator
  • Thank you again for your help!
    Thursday, April 16, 2015 6:17 AM