Match multiple substrings in a cell against a lookup table and return concatenated values

Steve,

Welcome to the Board.

Excel is not great when it comes to working with string Arrays so I would suggest you go down the UDF route - one example might be:

Function ReplaceStr(rngOrig As Range, rngReplace As Range, Optional strDelim As String = ",") As Variant
    Dim vTemp As Variant
    Dim lngR As Long
    vTemp = Split(rngOrig(1), strDelim)
    For lngR = LBound(vTemp) To UBound(vTemp) Step 1
        On Error Resume Next
        vTemp(lngR) = Application.VLookup(vTemp(lngR), rngReplace, 2, 0)
        On Error GoTo 0
        If Not IsError(vTemp) Then
            If InStr(1, ReplaceStr, vTemp(lngR), vbTextCompare) = 0 Then
                ReplaceStr = ReplaceStr & strDelim & vTemp(lngR)
            End If
        End If
    Next lngR
    ReplaceStr = Replace(ReplaceStr, ",", "", 1, 1)
End Function

The above would be stored in a Standard Module in VBE - once in place (and file stored as either .xls or .xlsm) you can then use like any other function though macros would need to be enabled - example usage:

Formula:

B1:

=REPLACESTR(A1,lookups!$A$2:$B$18)

There are numerous approaches to this type of issue -- though 90% will be using VBA for sake of efficiency.