VBA Lookup from Table and Replace

[ryancgarrett]

I'm trying to create a function that will function similar to vlookup, but will replace the value instead of return it. The other caveat is there are multiple strings to be searched and replaced within a single cell:

	A	B	C	D	E	F
1	Lookup Table			String		Result
2	Chevy	A		Chevy,Dodge		A,B
3	Dodge	B		Chevy,Toyota,Dodge		A,R,B
4	Toyota	R		Toyota,Dodge		R,B

<tbody>
</tbody>

I'm thinking something along the lines of =LookReplace(StringCell,LookupTable,LookupResultColumn), i.e. in the table above Cell F2 would contain =LookupReplace(D2,A1:B4,2)

I know I need to use the split function to split the strings and work with the individual strings, but I'm lost as to the lookup portion.

 

[Yongle]

Is this what you want

Formula in F2 copied down

=LookUpReplace(D2,A:A)

Function LookUpReplace(aString As String, aList As Range)
    Dim arr, c As Long, aMatch As Variant, aStr As String, aVal As String
    arr = Split(aString, ",")

    For c = 0 To UBound(arr)
        aVal = Trim(arr(c))
        On Error Resume Next
            aMatch = aList.Find(aVal, lookat:=xlWhole).Offset(, 1).Value
            If Err.Number > 0 Then aMatch = aVal
            If aStr = vbNullString Then aStr = aMatch Else aStr = aStr & "," & aMatch
        On Error GoTo 0
    Next c
    LookUpReplace = aStr
End Function

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​
1​	Lookup Table	​		String		Result
2​	chevy	A​		Chevy,Dodge		A ,B	=LookUpReplace(D2,A:A)​
3​	Dodge	B​		Chevy,Toyota,Dodge		A ,R ,B
4​	Toyota	R​		Toyota,Dodge		R ,B
5​		​		Toyota, Dodge		R ,B
6​		​		Toyota, Ford		R ,Ford
7​		​

Sheet: Sheet1

 

[pgc01]

Hi

Another option.

With Yongle's layout, in F2:

=LookUpReplace(D2,A:B)

Function LookUpReplace(s As String, rList As Range) As String
Dim v As Variant
With Application
    v = Split(.Trim(s), ",")
    LookUpReplace = Join(.IfError(.VLookup(v, rList, 2, 0), v), ",")
End With
End Function