Forum Discussion

RaeesaB's avatar
RaeesaB
Copper Contributor
Oct 17, 2022

Fetching VLOOKUP value with embedded hyperlink

I am trying to fetch a value from another sheet in the workbook from a cell which also has a hyperlink embedded. However, VLOOKUP only fetches the text and not the embedded hyperlink. How to get it t...
excel
Formulas and Functions
RaeesaB's avatar
RaeesaB
Copper Contributor
Oct 17, 2022

dscheikey 

Thanks! Could you explain your recommendation a bit?

"A possible approach would be to create the LINK in your source data with the HYPERLINK() function and to search for the coordinates of the cell instead of the cell content with VLOOKUP()."

How do you mean? Do you mean my source data should be the URL I want to link to? What would be the purpose of searching for cell coordinates? Which function would I use for this?

 

dscheikey's avatar
dscheikey
Bronze Contributor
Oct 17, 2022

RaeesaB 

I have made you a small example file that illustrates my solution. In H2 you can search for a link number. In A2:B6 are the data.

With the formula you get the cell entry with the link:

=LET(a,FORMULATEXT(INDIRECT("B"&XMATCH(H2,A2:A6)+1)),HYPERLINK(CHOOSECOLS(TEXTSPLIT(a,CHAR(34)),2),CHOOSECOLS(TEXTSPLIT(a,CHAR(34)),4)))

This only works if you have created the link with the HYPERLINK() function.

I have also made a variant without TEXTSPLIT() and CHOOSECOLS(), in case you don't use such modern Excel version or Excel for the web.

 

  • RaeesaB's avatar
    RaeesaB
    Copper Contributor
    Oct 17, 2022
    Thank you so much for taking the time to do this!! I'm so so grateful. If I understand correctly, this only works if links are created with HYPERLINK function as you specified. Sadly this is not the case for my data base 😞
    • dscheikey's avatar
      dscheikey
      Bronze Contributor
      Oct 18, 2022

      RaeesaB 

      Hi! If you haven't found a better solution yet, I can offer you to convert your cells with the links into functions with HYPERLINK(). I have found a macro for this on the net. However, it only works with LibreOffice Calc. I'm sure you can find one for Excel. But since I use Excel for the web, I cannot design / execute macros in Excel.


      Copy your cells with the links into a new worksheet and send it to me. I will convert and send it back to you if you want.

      It's just an offer to get you started.

       

Resources