Forum Discussion
Fetching VLOOKUP value with embedded hyperlink
VLOOKUP() or other LOOKUP() functions will only be able to output the displayed text or a value. If your cell content is also the link, e.g. "www.microsoft.com", then you can use the HYPERLINK() function within VLOOKUP().
However, if you use a different text and the hyperlink is not the same as the cell content, then it becomes much more complicated.
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(). Here you could then read out the hyperlink and the displayed text with FORMULATEXT().
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?
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.
- 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 😞
