Nos seus comentários, você mencionou que agora tem acesso a uma versão do Excel que pode executar o VBa.
Isso é o VBa e faz o que eu acho que você quer. Eu incluí as capturas de tela.
Deixei alguns comentários no código, a primeira seção que você pode precisar atualizar, mas os comentários devem ajudá-lo.
Lembre-se de fazer um backup do arquivo primeiro, pois não há recurso de desfazer!
Com base nos seus comentários, estou usando os nomes reais da planilha!
Option Explicit
Sub WalkThePlank()
'hear ye, only edit this top past of walk the plank
'Remember scurvy sea dog, there is no UNDO so take a copy of the file first as a back up
Dim worksheet1 As String
worksheet1 = "Image names" 'The name of the work sheet which has only codes
Dim worksheet1Column As String
worksheet1Column = "A" 'Argghh, the name of the column you use in worksheet1
Dim worksheet2 As String
worksheet2 = "LMFD products" 'The name of the worksheet with things like CC972 CC973 (Rear).jpg
Dim worksheet2Column As String
worksheet2Column = "A" 'Argghh, the name of the column you use in worksheet2
Dim resultsWorksheet As String
resultsWorksheet = "LMFD products" 'C'pan, this is where you put the results
Dim resultsWorksheetColumn As String
resultsWorksheetColumn = "C" 'Argghh, the name of the column you use in worksheet2
'hear ye, walk below and I'll feed ye to the sharks
Application.ScreenUpdating = False
Dim row As Integer
row = 2 'The starting row with values to be looked up
Do While (Worksheets(worksheet1).Range(worksheet1Column & row).Value <> "")
Dim result As String
result = ""
Dim lookupValue As String
lookupValue = Worksheets(worksheet1).Range(worksheet1Column & row).Value
Dim otherRow As Integer
otherRow = 2 'The starting row of the .jpg colum
Dim startString As String
Dim endString As String
startString = ""
endString = ""
Do While (Worksheets(worksheet2).Range(worksheet2Column & otherRow).Value <> "")
Dim repoValue As String
repoValue = Worksheets(worksheet2).Range(worksheet2Column & otherRow).Value
If (InStr(repoValue, lookupValue)) Then
'we got treasure cap'ain
If (InStr(repoValue, "(")) Then
endString = Trim(endString) & Trim(repoValue) & "|"
Else
startString = Trim(startString) & Trim(repoValue) & "|"
End If
End If
otherRow = otherRow + 1
Loop
'check on the treasure, will we fine riches
If (startString <> "" And endString <> "") Then
result = Trim(startString & Left(endString, Len(endString) - 1))
Else
If (startString = "" And endString <> "") Then
result = Trim(Left(endString, Len(endString) - 1))
End If
If (endString = "" And startString <> "") Then
result = Trim(Left(startString, Len(startString) - 1))
End If
End If
Worksheets(resultsWorksheet).Range(resultsWorksheetColumn & row).Value = result ' X Marks the spot
row = row + 1
Loop
End Sub
Minha planilha1 (antes da execução do VBa)
Eminhaplanilha2
E o resultado é