VLOOKUP & SEARCH Functions

Question

Is there a way to combine a VLOOKUP with a SEARCH function? For example, in one column I have a bunch of unregulated comments referring to another facility and/or city. I have a list of all possible cities and facilities but am not sure how to tell format the equation to say look in this string of words for a word that matches this table of cities. Is this possible? I can try to mock up the data if that would be helpful!

hansvogelaar · Answer

Lou_117&nbsp;
It would be helpful if you could provide an example of what the data look like and of what the expected result would be.

lou_117 · Answer

HansVogelaar&nbsp;Of course! I attached a file with an example that the data comes in a very unregulated format as they are "comments" and then I listed all the cities within the state and all the hospitals. I did not list all the health systems for the overarching organization but did a few to give you an idea.&nbsp;&nbsp;I am hoping to have a formula that scans the "comment" field for any of the city names, and facilities, (I will list states so it can populate as "Outside of State") but if a city is not found it returns "" so the cell appears blank. However, if the facility is listed but the city is now, it would be great if it could return "Unknown". I hope this helps!

hansvogelaar · Answer

Lou_117&nbsp;
Thanks. In row 2 on Sheet 1, you have Beaumont as Goal 1 and Detroit as Goal 2.
Sheet2 has 9 instances of Beaumont Hospital, but none of them is in Detroit.
How does that work?

lou_117 · Answer

HansVogelaar&nbsp;Sorry I was writing examples a little fast and just added a random city example to pull from. However, I have corrected that to reflect an example from the selections.&nbsp;

hansvogelaar · Answer

Lou_117&nbsp;
Here is a solution using a custom VBA function GetLocation. It is entered in B2:C2 as an array formula, then filled down.
Function GetLocation(s As String)
    Dim wsh As Worksheet
    Dim i As Long
    Dim j As Long
    Dim parts() As String
    Dim rngC As Range
    Dim addr As String
    Dim rngF As Range
    Dim ret(1 To 2) As String
    ret(1) = "Unknown"
    ret(2) = "Unknown"
    Set wsh = Worksheets("Sheet2")
    parts = Split(s)
    For i = 0 To UBound(parts)
        Set rngC = wsh.Range("H:H").Find(What:=parts(i), _
            LookAt:=xlPart, MatchCase:=False)
        If Not rngC Is Nothing Then
            addr = rngC.Address
            Do
                For j = 0 To UBound(parts)
                    If j &lt;&gt; i Then
                        Set rngF = rngC.Offset(0, -3).Resize(1, 2).Find _
                            (What:=parts(j), LookAt:=xlPart, MatchCase:=False)
                        If Not rngF Is Nothing Then
                            ret(1) = rngF.Value
                            ret(2) = rngC.Value
                            GoTo ExitHere
                        End If
                    End If
                Next j
                Set rngC = wsh.Range("H:H").Find(What:=parts(i), _
                    After:=rngC, LookAt:=xlPart, MatchCase:=False)
                If rngC Is Nothing Then Exit Do
            Loop Until rngC.Address = addr
        End If
        Set rngF = wsh.Range("E:F").Find(What:=parts(i), _
            LookAt:=xlPart, SearchOrder:=xlByRows)
        If Not rngF Is Nothing Then
            ret(1) = rngF.Value
            addr = rngF.Address
            Do
                Set rngC = wsh.Range("H" &amp; rngF.Row)
                For j = 0 To UBound(parts)
                    If j &lt;&gt; i Then
                        If UCase(rngC.Value) Like "*" &amp; UCase(parts(j)) &amp; "*" Then
                            ret(2) = rngC.Value
                            GoTo ExitHere
                        End If
                    End If
                Next j
                Set rngF = wsh.Range("E:F").Find(What:=parts(i), _
                    After:=rngF, LookAt:=xlPart, SearchOrder:=xlByRows)
                If rngF Is Nothing Then Exit Do
            Loop Until rngF.Address = addr
        End If
    Next i
ExitHere:
    GetLocation = ret
End Function
See the attached version.

Forum Discussion

VLOOKUP & SEARCH Functions

5 Replies

Resources