How to vlookup comma separated in one cell?

Question

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

What have you tried so far? Where did you run into trouble? Please include that in your question. — braX
You can do this with a simple nested "IF function" or, imho cleaner, the "Switch function". — ceci

VBasic2008 VBasic2008 · Accepted Answer · 2021-10-18T04:50:00

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)
```

How to vlookup comma separated in one cell?

1 Answers

Replace Delimited (UDF)