vlookup question/help needed matching multiple image names to model number.

Hello all, sorry I posted this in the wrong section before!

I basically want to transfer image names from the second sheet into the first sheet in another column and match by the model number as the model numbers are the same as the image names in the second sheet(kindof!) but most do have names such as model number-detail.jpg and model number-roomset.jpg etc

I have 2 sheets in one book, in column A in the first sheet I have model numbers named like ALN-SDC and in the second sheet I have image names in column A

Sheet 1

Model numbers - Images
ALN-SDC -
BLN-SDV -
CKM-SDK

Sheet 2:

Images
ALN-SDC.jpg
ALN-SDC-detail.jpg
ALN-SDC-roomset.jpg
BLN-SDV.jpg
BLN-SDV-roomset.jpg
CKM-SDK.jpg
CKM-SDK-detail.jpg
CKM-SDK-roomset.jpg


is it possible to make sheet one separate the matching images by a pipe separator symbol "|" in the same cell like this?

Model numbers - Images
ALN-SDC - ALN-SDC.jpg|ALN-SDC-detail.jpg|ALN-SDC-roomset.jpg
BLN-SDV - BLN-SDV.jpg|BLN-SDV-roomset.jpg
CKM-SDK - CKM-SDK.jpg|CKM-SDK-detail.jpg|CKM-SDK-roomset.jpg



If I take out the .jpg, out of the images I can match about 250 images to model numbers out of over 1000 using an exact match vlookup formula and the workaround is to create another column after with .jpg then merge the results.

However is there a way to also match the image names that are slighly different but still contain the model number? A approxiamate search creates too many duplicates and mismatches.

Kind regards and much appreciated for any advice

See if this works as you intended.

Originally Posted by WHER

See if this works as you intended.

Hey thank you, great script! Works perfect in your example, but it threw an error on my list here:

str = Left(str, Len(str) - 1)

I fixed it by adding:

On Error Resume Next
str = Left(str, Len(str) - 1)

Cheers, much appreciated!