Forum Discussion

Lou_117's avatar
Lou_117
Copper Contributor
Oct 22, 2020

VLOOKUP & SEARCH Functions

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 ...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Oct 22, 2020

Lou_117 

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's avatar
Lou_117
Copper Contributor
Oct 23, 2020

HansVogelaar 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. 

 

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's avatar
    HansVogelaar
    MVP
    Oct 23, 2020

    Lou_117 

    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's avatar
      Lou_117
      Copper Contributor
      Oct 23, 2020

      HansVogelaar 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. 

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 23, 2020

        Lou_117 

        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 <> 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" & rngF.Row)
                For j = 0 To UBound(parts)
                    If j <> i Then
                        If UCase(rngC.Value) Like "*" & UCase(parts(j)) & "*" 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.

Resources