Lookup to find a match between two sheets and hyperlink the corresponding column value

crkbrtk  try the attached.  I updated the formula in a couple places:

=LET(
  JiraIDs,  A5,
  Key,      Sheet2!$A$2:$A$100, Links, Sheet2!$B$2:$B$100,
  SheetRef, SUBSTITUTE(SUBSTITUTE(TEXTBEFORE(CELL("address",INDEX(Links,1)),"!"),"'",""),"]","]'")&"'!",
  found,    XMATCH("*"& JiraIDs &"*", Links,2),
  IF(ISNUMBER(found),
    HYPERLINK(SheetRef&ADDRESS(ROW(INDEX(Links,found)),COLUMN(INDEX(Links,1))),INDEX(Key,found)),
    HYPERLINK("","No Match")
  )
)

so line 3 above needs to point to the actual range for the key and links (before you had 'Sheet 2' which had a space in the tab name so excel didn't recognize that.  I would recommend updating sheet 2 data to be in a named TABLE (format as table on the home tab and then click in the table tab and give it a name) so then you can use 'Structured Reference' like LinkTable[Links] but that might be something to learn later.

I also added line 4 because line 7 was using the name 'SheetRef' found in the other workbook as that formula we created to generate how to properly reference Sheet2 (or whatever that sheet's name is in your workbook).  So I added line 4 and added that calculation.  I would actually recommend you create a workbook name so excel only has to do that 1 time instead of every cell but again that might be something you will need to learn more about.

Finally I updated line 7 to use COLUMN(INDEX(Links,1)) because before I hardcoded 2 for column B but if you move this to another book or change the reference that Links in line 3 points to this new version will automatically update.

PS please NOTE that this formula uses CELL() function and that function is NOT supported  in the online version of excel. but neither is the HYPERLINK() function for 'local' links (i.e. to another cell inside the workbook)