Copy links from one sheet to another

Problem Example.xlsx

Hi Experts,
I am new to excel forum ad this is my first post.
I have 2 sheets- Sheet 1 and Links Sheet
I have some items in Sheet 1 in column B and C.
Second links sheet contains links to Images.
The link contains the last code of Item same for eg IMG_0365.JPG

I want the link present in the second sheet gets copied to same coded
Item in sheet 1 in column D

Please find the attached excel file and help me out guys and all expert members.
Thanks.

You can actually extract the "JPG" string from the B:C columns and retrieve the hyperlink using just a formula, put this in D2 and copy down... some values won't work, but the rows D11:D17 will at first:

=HYPERLINK(INDEX(Links!B:B,MATCH("*"&MID(B2,FIND("IMG",B2),FIND("JPG",B2)+4-FIND("IMG",B2))&"*",Links!$B:$B,0)),"Link")


Now, we can use an IFERROR() to make it switch to column C when an error occurs column B:


=IFERROR(HYPERLINK(INDEX(Links!B:B,MATCH("*"&MID(B2,FIND("IMG",B2),FIND("JPG",B2)+4-FIND("IMG",B2))&"*",Links!$B:$B,0)),"Link"), HYPERLINK(INDEX(Links!C:C, MATCH("*" & MID(C2, FIND("IMG", C2), FIND("JPG", C2)+4-FIND("IMG", C2)) & "*", Links!$B:$B, 0)), "Link"))

URLs are notoriously ugly, so I use the word "Link" for the display, neat and preferable to the average user. But there are time you as the designer might want to see the URL... just remove the LINK (and the comma preceding) and the cell will display the URL itself.

(underlined above in the original post)