In your comments, you mentioned you now have access to a version of Excel which can run VBa.
This is VBa, and does what I think you want. I have included screen shots.
I have left some comments in the code, the first section you may need to update but the comments should help you out.
Remember, take a back up of your file first as there is no undo feature!
Based upon your comments, I'm using the actual worksheet names!
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
My Worksheet1 (before the VBa is run)
And my Worksheet2
And the result is