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

5 Replies

# Re: VLOOKUP & SEARCH Functions

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

# Re: VLOOKUP & SEARCH Functions

@Hans Vogelaar 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!

# Re: VLOOKUP & SEARCH Functions

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?

# Re: VLOOKUP & SEARCH Functions

@Hans Vogelaar 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.

# Re: VLOOKUP & SEARCH Functions

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