Fill Sheet1 ID with value of ID from Sheet2

Question

im new here and i have a problem with filling data from another sheet.

Sheet1 looks like this:

 _|___A___|___B___|___C___|
 1|   1   |John   |1,35,12|
 2|   2   |Derek  |45,2,1 |
 and so....

Sheet2

 _|___A___|___B___|
 1|   1   |Hammer |
 2|   2   |Nails  |
        . . .
12|   12  |Car    |
        . . . 
35|   35  |Rope   |
        . . .
45|   45  |Vase   |

i need to replace ID's in Sheet1 with names from Sheet2 (separated by "," or ", ") to get this:

 _|___A___|___B___|_______C_________|
 1|   1   |John   |Hammer,Rope,Car  |
 2|   2   |Derek  |Vase,Nails,Hammer|
 and so....

i dont know if its even possible but if is please help me

ty :)

Answer 1

add this code to new module in excel:

Function SplitThenFind(cell As String, sourceColumn As Range)

    Dim myArray As Variant
    Dim element As Variant
    Dim result As String
    Dim findResult As Range

    myArray = Split(cell, ",")

    For Each element In myArray

        Set findResult = Application.Worksheets(sourceColumn.Worksheet.Index).Range(sourceColumn.Address).Find(element, Lookat:=xlWhole)

        If Not (findResult Is Nothing) Then
            ' with offset you can select the column you need
            result = result & findResult.Cells.Offset(0, 1).Value & ","
        End If
    Next

    If Len(result) > 0 Then
      result = Left(result, Len(result) - 1)
    End If

SplitThenFind = result

End Function