snippets/microsoft/ms-office/excel/functions/vreplace.vba

Function vreplace(str As String, insep As String, outsep As String, rng As Range, idx As Integer) As String
    ' Takes a (sep) seperated string (str) like "banana,carrot,apple",
    ' replaces the elements using vlookup with values from range (rng), column (idx)
    ' and return as (sep) seperated string

    Dim strArray() As String
    Dim i As Integer
    Dim crtStr As String
    Dim coll As New Collection
    Set coll = New Collection

    strArray = (Split(str, insep))

    For i = LBound(strArray) To UBound(strArray)
        'Adding the same value to a collection turns to an error.
        'Handling the error will skip adding the string to the output
        crtStr = Application.VLookup(strArray(i), rng, idx, False)
        On Error GoTo Skip:
        coll.Add crtStr, crtStr
        vreplace = vreplace & outsep & crtStr
Skip: Resume Continue:
Continue:
    Next
    
    vreplace = Right(vreplace, Len(vreplace) - 1)
End Function