Hyperlink function to web link

[expeo]

I have exported a worksheet from a SharePoint library, it has the file name and the hyperlink to the SharePoint location already.

I am attempting to match these to another link that has the same list of file names so I used the command

=HYPERLINK(VLOOKUP(G2,A:A,1,FALSE),G2)

G2 is the file name reference, column A includes the exported file name with the hyperlink

Now if I click on the original cell (that was exported from SharePoint), it directs me to the location with no issues. But if I click on the new cell with the function, I get the message "Cannot open the specified file."

How can I fix this? Is there an alternative solution to this, possibly something in VBA?

 

[Roderick_E]

What is the exact url... is it preceded by http:// or what? I suspect that may be the problem.

 

[expeo]

https://sitename.sharepoint.com/sites/102/name/libraryname1 libraryname2/filename1 filename2 .pdf


(broken up below)
https: //sitename.sharepoint .com/
sites/102/name/libraryname1%20libraryname2/filename1%20filename2%20.pdf

If this format is incorrect, how can I fix this (without manually editing each one)?

 

[Roderick_E]

hmmm without the =hyperlink wrapper does it return the entire link?

=[COLOR=#333333]VLOOKUP(G2,A:A,1,FALSE)[/COLOR]