Categorize Items in a table against matching text in another table with partial matches.

[mvulc]

First of all thank you for taking the time to read my question, and provide an answer if possible.
My apologies if this is already answered on the forum, I did search for it but could not find how to do this sort of match either with either a vlookup or index match, as in my case I'm searching for the existence of any one of a number of values.

Here's what I'm trying to accomplish.

In the table below, assume columns A,B,C are a table, and columns F,G are a separate table.
I want to fill in column C with the "ticket type" value as listed in column G, so long as there is a match for any of the values in column F in the text within column B.
So the results should be
C2 = "Backup Failure"
C3 = "Backup Failure"
C4 = "Restore Request"
C5 = "Ping Timeout"
C6 = "Hardware Failure"
C7 = "Hardware Failure"


[TABLE="class: grid, width: 737"]
<tbody>[TR]
[TD]Ticket[/TD]
[TD]Title[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD][/TD]
[TD]Match Text[/TD]
[TD]Ticket Type[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Backup Failure on Server A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Backup[/TD]
[TD]Backup Failure[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Server B Failed Backup[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Restore[/TD]
[TD]Restore Request[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Restore Directory on Server C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ping[/TD]
[TD]Ping Timeout[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Ping Timeout Server D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hard Drive[/TD]
[TD]Hardware Failure[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Server D Failed Hard Drive[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Failed Memory[/TD]
[TD]Hardware Failure[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Server E Failed Memory[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sericom]

Here's a function that should do what you're after:

Public Function ticketType(matchText As Range, title As Range)
    Dim i As Integer
    i = 2
    For Each rng In matchText
        If InStr(UCase(title.Value), UCase(rng.Value)) Then
            ticketType = Range("G" & i)
        End If
        i = i + 1
    Next
End Function

Then use this formula in C2 and drag down:

=ticketType($F$2:$F$6,B2)

 

[yshridhar]

c2==LOOKUP(9^9,SEARCH(F$2:F$6,B2),G$2:G$6)
copy down as per your data

Regards
Sridhar

 

[Aladin Akyurek]

...In the table below, assume columns A,B,C are a table, and columns F,G are a separate table.
I want to fill in column C with the "ticket type" value as listed in column G, so long as there is a match for any of the values in column F in the text within column B.
[...]

Define BigNum as referring to:

=9.99999999999999E+30

In C2 enter and copy down:

=LOOKUP(BigNum,SEARCH(" "&$F$2:$F$6&" "," "&B2&" "),$G$2:$G$6)

 

[mvulc]

You guys are awesome!
Thank you so much.

 

[Aladin Akyurek]

You guys are awesome!
Thank you so much.

You are welcome. A small edit though: BigNum is =9.99999999999999E+307.