Search longer string within an array of shorter st

Question

Assuming I want to create a lookup function that searches certain string, let's says "dog;cat" and I want it to go through a certain array, and return column b in that array in the row that partially matches that string, for example:

Horse 5
Cat   2
Wolf  3

So in this example I'd expect to get "2". Is it possible? Searched the net for answers, but couldn't find anything helpful. Please note the string I'm searching can be complex, for example - "dog;shark;cat;pigeon"... So it has to be a universal solution.

Any help will be appreciated :)

Answer 1

With data like:

Put the "long string" in say D1 and in E1 enter:

=VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH($A$1:$A$6,D1),$A$1:$A$6),A1:B6,2,FALSE)

Notes:

• the LOOKUP() returns "cat"
• VLOOKUP() gets the associated digit

Usually we use SEARCH() or FIND() or MATCH() to see if a big string contains a little string....................in this case we want to find which little string is in the big string !

Answer 2

This will work if you're always returning a single numerical value:

=SUM(IF(ISERR(SEARCH(search_range,search_for,1)),"",values_range))

For your example data, search_range is the range holding Horse, Cat, Wolf; search_for holds "dog;cat"; and values_range is the range holding 5, 2, 3.

Hope that helps

Answer 3

You could split your array to list each variable in a separate row then use a VLOOKUP function to compare each value to your table. (The table would have to be sorted into alphabetical order first.)

Extending your example, if you split the array into column C, you could use the following fomula in the first row of column D:

=VLOOKUP(C1,$A$1:$B$3,2,FALSE)

Using the absolute reference to the lookup table will allow you to copy the formula into additional rows. The result for your suggested values will be:

Cat   | 2 | dog | #N/A
Horse | 5 | cat | 2
Wolf  | 3 | 

Answer 4

Try this UDF out.

SearchFor is what you want to find. This is a delimited text string, for example "cat;dog;horse".

SearchIn is the cell range you want to look in.

The last parameter is the delimiter that will split the SearchFor variable. In the example above "cat;dog;horse" the delimiter would be ";".

Public Function FindString(SearchFor As String, _
                           SearchIn As Range, _
                           Delim As String) as String

    Dim MyArray()       As String
    Dim i               As Long
    Dim FoundCounter    As Long
    Dim Position        As Long

    MyArray = Split(SearchFor, Delim, , vbTextCompare)

    FoundCounter = 0
    For i = UBound(MyArray) To LBound(MyArray) Step -1
        Do
            If FoundCounter = 0 Then
                Position = InStr(1, SearchIn.Value, MyArray(i), vbTextCompare)
            Else
                Position = InStr(Position + 1, SearchIn.Value, MyArray(i), vbTextCompare)
            End If

            If Position = 0 Then
                Exit Do
            Else
                FoundCounter = FoundCounter + 1
            End If
        Loop
        FindString = MyArray(i) & " " & FoundCounter & " " & FindString
        FoundCounter = 0
    Next i

End Function