Hyperlink Function w/ Match/Index or Vlookup

[edins4250]

Greetings all,

Since SharePoint updates links every time a PDF is replaced, I'm looking to use a HYPERLINK function that'll hopefully only need to be updated in one tab (links column C) and via a (hyperlink/vlookup) formula in the other 3 tabs it'll update that same link where that same pdf is linked (see attached excel sheet for details). 

I'd like the link to update in all the tabs for that program and the 'program name' to be clickable - which is the "friendly name" part of the function (column B in links)? 

If anyone has any insight on this, I'd greatly appreciate it. 

[#2 · Accepted solution]

This formula (in col B of the three sheets next to sheet Links) should get you going:
=HYPERLINK(XLOOKUP(A2,Links!$A$2:$A$9,Links!$C$2:$C$9),XLOOKUP(A2,Links!$A$2:$A$9,Links!$B$2:$B$9))

[#3]

@Jan Karel Pieterse thank you so much this works! Another solution I found to work is essentially the same formula as yours but a vlookup instead of xlookup. 

[#4]

Xlookup is to be preferred unless this must also work on older versions of Excel