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