Oct 22 2020 12:19 PM
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!
Oct 22 2020 12:49 PM
It would be helpful if you could provide an example of what the data look like and of what the expected result would be.
Oct 23 2020 04:46 AM
@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!
Oct 23 2020 05:22 AM
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?
Oct 23 2020 05:38 AM
@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.
Oct 23 2020 09:33 AM
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.