Forum Discussion
RaeesaB
Oct 17, 2022Copper Contributor
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 to fetch the embedded hyperlink?
- SST12Copper Contributor
Have you had a viable solution? Since hyperlink needs two parameters, you need to nest two vlookups; one vlookup for the link location and one vlookup for the friendly name.
=HYPERLINK(VLOOKUP(lookup value,table array,column,false),VLOOKUP())Here was mine:
- Reezky77Copper Contributor
Hi, have you found a solution? I'm also having the same problem here 😞
- wtweathersCopper Contributor
Reezky77 I have seen multiple requests for help on this subject - but none of the answers make sense! HELP! So allow me to restate challenge. I have spreadsheet "Documents" in which Cell A2 has document name WWWZZZ and Cell B2 has the link to where that document is saved such as https://wtweathers/wwwzzz. I have another spreadsheet "Process" that references document WWWZZZ and I want to add a column (Column Z) that will use VLOOKUP for "Documents" spreadsheet to populate the link in B2 to the "Process" spreadsheet in Column Z2. The vlookup formula shows the path in text, but will not allow a hyperlink and when I attempt to copy (to paste as URL), it only copies the vlookup formula. I am attempting to have the hyperlink in Documents B2 be displayed as a hyperlink in Process Z2. Anyone? Thanks!!!!
- dscheikeyBronze Contributor
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().
- RaeesaBCopper Contributor
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?
- dscheikeyBronze Contributor
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.
- Riny_van_EekelenPlatinum Contributor
RaeesaB Wrap the VLOOKUP in a HYPERLINK function. Something like this:
=HYPERLINK(VLOOKUP(..............))