Approximate Searching for Multiple Items

[Shawn Miller]

Hello,

I have tried searching the forums for something like this and I feel like I am overlooking something easy. I have data with descriptions that include vendor names. My goal is to do some kind of lookup that looks into one table of names and if a cell contains any of those names that return that name. So for example, if "Apple" is in my lookup table and a cell in my data says "Apple invoice 1234", then I would want to return "Apple" in my lookup.

I have tried a couple things, attached is an example file. The only thing I have found was to do a simple TRUE/FALSE check.

Is there a relatively easy way to accomplish what I am trying to do?

 

[Haz]

Formula in C11
=INDEX($A$4:$A$7,MATCH(0,INDEX(SEARCH($A$4:$A$7,A11),),-1))

 

[AlanSidman]

Option Explicit

Sub Shawn()
    Dim crit As String
    Dim lr As Long, i As Long
    crit = InputBox("What do you want to search for?")
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 11 To lr
        If InStr(Range("A" & i), crit) > 0 Then
            Range("A" & i).Offset(0, 1) = True
            Range("A" & i).Offset(0, 2) = crit
        End If
    Next i
    Application.ScreenUpdating = True

End Sub

 

[bosco_yip]

Or……………..

In C11, copied down :

=LOOKUP(1,-SEARCH(A$4:A$7,A11),A$4:A$7)

Regards
Bosco

 

[Shawn Miller]

Haz/Alan/Bosco - sorry for the delay, for some reason I didnt get an email notice and checked in to see results. Thank you for the help these are great solutions! I rarely use the SEARCH function and never thought through solutions like these. Bosco - thank you for the Macro, I like that it would be dynamic.

 

[AlanSidman]

Thanks for the feedback