VBA function to return a single matching value from comma separated lookup up value

[bijicha]

Hi friends,

Please help me with a VBA function to vlookup, to return single value from a comma separated cell.

Workbook1, sheet1

A B
1,2,3,4 Sun
5,6,7,8 Moon


Workbook2, sheet1

A B
1 Sun (Vlookup Function need to get "Sun")
2 Sun
3 Sun
4 Sun
5 Moon (Vlookup Function need to get "Moon")
6 Moon
7 Moon
8 Moon

I need to vlookup to workbook2 sheet1 coloumB and to get the result in single vlaue.

Please help me

 

[PCosta87]

Hi,

Something like this perhaps?

Sub test()

    Dim c1, c2 As Range
   
    For Each c1 In Range("A2:A9")
        For Each c2 In Workbooks("Workbook1.xlsx").Sheets(1).Range("A2:A3")
            If InStr(c2, c1) > 0 Then
                c1.Offset(, 1) = c2.Offset(, 1)
            End If
        Next c2
    Next c1

End Sub

CVS in WB1... A2:B3
Lookup range in WB2... A2:A9
Code in WB2

Hope this helps

 

[shrivallabha]

Following approach will also work.
Book1 & Book2 data starts in cell A1
=LOOKUP(99,FIND(","&A1&",",","&[Book1]Sheet1!$A$1:$A$2&",",1),[Book1]Sheet1!$B$1:$B$2)

PCosta's code may give erroneous results in some cases e.g.
if first row data is in Workbook1 is changed from
1,2,3,4
11,2,3,4
still for 1 in 2nd workbook it will return "a" as match value.