VBA Function to return a value or Multiple values

[Stephen_IV]

Good afternoon,
I have data in A1:A5 that looks like this (just a sample). In B1 let’s say I would house 101 and in C1 I would like to return Red. One caveat is that if I lookup 102 then I would like to return Green, Blue. Thanks in advance!

Red:(101,103,105)
Green:(102, 104,105)
Black:(104,111,115,117,119)
Blue:(102,162,169)

 

[Yongle]

Add this function in a standard module

Function GetColours(LookFor As String, rng As Range)
Dim cel As Range, txt As String, clr As String, a As Variant, m As String
For Each cel In rng
    txt = cel.Value
    If (txt) <> vbNullString Then
        clr = Left(txt, InStr(txt, ":") - 1)
        txt = Right(txt, Len(txt) - InStr(txt, "("))
        txt = Left(txt, InStr(txt, ")") - 1)
    
        For Each a In Split(txt, ",")
            If LookFor = Trim(a) Then
                If m = "" Then m = clr Else m = m & ", " & clr
                Exit For
            End If
        Next
    End If
Next cel
    GetColours = m
End Function

Put this formula in cell C1
=getcolours(B1,A2:A5)

 

[Stephen_IV]

Thank you so much Yongle! That works perfectly! Thank you again for helping me out with this! I appreciate it!

 

[Yongle]

Thanks for the feedback