The problem with that approach is that you're likely to end up with false matches.
By trying to match an array that only contains sub-string(s) of the strings you're trying to match on, you are, in effect, asking the array to search the search string before it can tell you which search returns a match. Without a lot of work, that approach is only ever going to return the first of any possible number of matches.
To do that, it's probably best to employ a User-Defined Function, such as the following, which you can add to an ordinary code module in the workbook:
Code:
Function MatchSubString(StrVal As Variant, Arr As Range, Optional MatchCase As Variant) As Variant
Dim i As Long, j As Long, k As Long, l As Long
Dim StrArr As String, StrItm As String, Rng As Range
For Each Rng In Arr.Cells
StrArr = StrArr & Rng.Text & ","
Next
If MatchCase = 1 Then
MatchCase = vbBinaryCompare
Else
MatchCase = vbTextCompare
End If
MatchSubString = "N/A"
For i = 0 To UBound(Split(StrArr, ","))
StrItm = Split(StrArr, ",")(i)
If InStr(StrItm, StrVal) > 0 Then
MatchSubString = i + 1
Exit Function
ElseIf Len(StrVal) > Len(StrItm) Then
For j = 1 To Len(StrVal)
If InStr(1, StrItm, Left(StrVal, j), MatchCase) > 0 Then
If j > k Then
k = j
MatchSubString = i + 1
End If
End If
Next
End If
Next
End Function
Then you can use the function just as you would any other formula. The MatchSubString function takes three arguments: the string or range of the string to match; the range of the arrary to match agains; and, optionally, whether the match should be case-sensitive. For example:
=MatchSubString(S11,$A$2:$A$7,0) or =MatchSubString(S11,$A$2:$A$7,0) would return '2' for "End of life span" in S11.
You can nest the function in other Excel functions, too:
=OFFSET($A$2,MatchSubString(S11,$A$2:$A$7,0)-1,1)