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"

Ticket	Title	Type			Match Text	Ticket Type
1	Backup Failure on Server A				Backup	Backup Failure
2	Server B Failed Backup				Restore	Restore Request
3	Restore Directory on Server C				Ping	Ping Timeout
4	Ping Timeout Server D				Hard Drive	Hardware Failure
5	Server D Failed Hard Drive				Failed Memory	Hardware Failure
6	Server E Failed Memory

<tbody>
</tbody>

 

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