I recently found this post, which is so close to my solution, but not quite there!
Extract the link text and URL from a hyperlinked cell
The issue with this custom formula (=LinkURL()) is that it can only accept a cell reference e.g. =LinkURL(A1). I want to be able to use a vlookup formula within this function like this:
In column G, I'm using the following formula:
=ARRAYFORMULA(IF(ROW(G:G)=1, "Retail price", IF(ISBLANK(B:B), "",vlookup(B:B,{'NEW - Product Analysis'!B:B,'NEW - Product Analysis'!E:E},2,false))))
Which correctly pulls specific column data from another sheet which is my main tab with the data. The data this pulls over is a hyperlinked cell, with a financial value as the string. I want to separate these in the destination sheet i.e. I want to pull the hyperlink into one column, and the value in a separate column. The only way I could think to do this is by using the =LinkURL(G2), but this does not work. I therefore tried to use vlookup to pull the data directly into the =LinkURL function but it would not accept a formula, only a cell reference.
On another note, I was also unable to get the =LinkURL function to accept the string from another cell to be used as the cell reference. Is this possible?
Apologies in advance if I have not clearly articulated what I want. Let me know and I will attempt to explain more clearly.
.toString()to the hyperlinked value of vlookup, then you get the "label" section of the hyperlink. – Tedinoz Nov 3 '19 at 9:11=ADDRESS(MATCH(E1,A1,0),2, 1). This will return $B$1. If you then run linkURL (webapps.stackexchange.com/a/89588/196152), then the formula will return the url. – Tedinoz Nov 3 '19 at 9:20LinkURLto get the URLs from the results of a built-in function likeVLOOKUPbecause they return values not references. IMHO the best approach is first get the URLs then do theVLOOKUP. – Rubén Nov 3 '19 at 14:17