How to vlookup comma separated in one cell?-CodePudding

I have worksheet

I want to fill in column G

I don't know what to do about this problem

what I want :

. If column F is filled with black , then column G is filled with S
. If column F is filled with yellow , then column G is filled with M
. If column F is filled with red, then column G is filled with XL

I use office 2010

CodePudding user response：

Replace Delimited (UDF)

In the Visual Basic Editor, copy the following code to a standard module:

Option Explicit

Function ReplaceDelimited( _
    ByVal SearchString As String, _
    ByVal ReadSubStrings As String, _
    ByVal WriteSubStrings As String, _
    Optional ByVal Delimiter As String = ",") _
As String
    On Error GoTo ClearError
    
    Dim Rss() As String: Rss = Split(ReadSubStrings, Delimiter)
    Dim mIndex As Variant: mIndex = Application.Match(SearchString, Rss, 0)
    If IsNumeric(mIndex) Then
        ReplaceDelimited = Split(WriteSubStrings, Delimiter)(mIndex - 1)
    End If
    
ProcExit:
    Exit Function
ClearError:
    Resume ProcExit
End Function

In Excel, for this particular case, in cell G2 use the following formula:
```
=ReplaceDelimited(F2,C2,D2)
```