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