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.

  • 1
    Have you tried the custom function in my answer to the referred question? – Rubén Nov 2 '19 at 16:03
  • Well I did, @Rubén, and when the target is the value of a vlookup, the function returns "undefined". – Tedinoz Nov 3 '19 at 8:49
  • I have a feeling that these fields are going to be fashioned piecemeal. FWIW#1, if you apply .toString() to the hyperlinked value of vlookup, then you get the "label" section of the hyperlink. – Tedinoz Nov 3 '19 at 9:11
  • @Rubén FWIW#2, if we assume that the VLOOKUP search key is in cell E1, the VLOOKUP range is A1:B1 (label in cell A1 and the hyperlink formula is in cell B1), and this formula is in Cell E3 =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:20
  • 1
    By the way it's not possible to use my custom function or LinkURLto get the URLs from the results of a built-in function like VLOOKUP because they return values not references. IMHO the best approach is first get the URLs then do the VLOOKUP. – Rubén Nov 3 '19 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.