Xlookup to return cell text + hyperlink

[Jobb1Excel]

Hey, im looking for a formula that returns the hyperlink from the result of the xlookup from another sheet.

i got a "sheet1" and "sheet2": In the "sheet1" im using xlookup to find job descriptions (each material number has a job description file linked to the cell), but when i use the xlookup to get the job description for each material number it only returns the cell text and not the hyperlink from the cell. I have tried =hyperlink with no luck...

Cell A1-A4 is the cells with hyperlink im using xlookup to find. But the result i get is example: 1 and no link.

 

[ExcelToDAX]

I am assuming the links in column A weren't created the HYPERLINK() function, correct? If so, you either have to use Office Scripts or VBA to accomplish this.

Here is a VBA function that I found here:

Function GetURL(cell As Range)
    GetURL = cell.Hyperlinks(1).Address
End Function

 

[Jobb1Excel]

I am assuming the links in column A weren't created the HYPERLINK() function, correct? If so, you either have to use Office Scripts or VBA to accomplish this.

Here is a VBA function that I found here:

Function GetURL(cell As Range)
    GetURL = cell.Hyperlinks(1).Address
End Function

I have already tried to use the VBA function with no luck. I dont think the links was created with the HYPERLINK() function.

 

[SunnyAlv]

May I ask a little before making the right formula,

1. What kind of "HYPERLINK" formula do you have (See Image)

2. Your excel uses commas or semicolons

 

[Jobb1Excel]

May I ask a little before making the right formula,

1. What kind of "HYPERLINK" formula do you have (See Image)

2. Your excel uses commas or semicolons

View attachment 103556

This is what the hyperlink looks like.

My xlookup returns "1" without the link.

 

[ExcelToDAX]

This is what the hyperlink looks like.

View attachment 103575
View attachment 103576

My xlookup returns "1" without the link.

the function I sent you should work. I said I assumed you AREN'T using the HYPERLINK function. The VBA function will work when it is like the screenshot you just posted.

 

[ebea]

the function I sent you should work. I said I assumed you AREN'T using the HYPERLINK function. The VBA function will work when it is like the screenshot you just posted.

This function, will only return the Link text, but not a functional Link. You can't get the Link to work, as Excel do not insert the Link itself.
If you Right click on the text, pulled out (looks like a Link), and choose Link, you will see in the opened Box, that the Link are missing.

 

[Alex Blakenburg]

Using the function that @ExcelToDAX provided see if this gives you any ideas ?
(so you will need to put the code in Post 2 in a standard module and save the workbook as xlsm or xlsb)
PS: I did have to manually format the range as Hyperlink

20231215 VBA Hyperlink Jobb1Excel not working.xlsm
	A	B	C	D	E	F
1	Lookup Array			Xlookup
2
3	Apple Link	Apple		Lookup -->	Apple	Apple Link
4	Orange Link	Orange
5	Pear Link	Pear
6	Kiwi Link	Kiwi
7
Lookup

Cell Formulas
Range	Formula
F3	F3	=LET(rCell,XLOOKUP(E3,$B$2:$B$10,$A$2:$A$10,""),IF(rCell<>"",HYPERLINK(GetURL(rCell),rCell),""))

 

[Jobb1Excel]

Using the function that @ExcelToDAX provided see if this gives you any ideas ?
(so you will need to put the code in Post 2 in a standard module and save the workbook as xlsm or xlsb)
PS: I did have to manually format the range as Hyperlink

20231215 VBA Hyperlink Jobb1Excel not working.xlsm
	A	B	C	D	E	F
1	Lookup Array			Xlookup
2
3	Apple Link	Apple		Lookup -->	Apple	Apple Link
4	Orange Link	Orange
5	Pear Link	Pear
6	Kiwi Link	Kiwi
7
Lookup

Cell Formulas
Range	Formula
F3	F3	=LET(rCell,XLOOKUP(E3,$B$2:$B$10,$A$2:$A$10,""),IF(rCell<>"",HYPERLINK(GetURL(rCell),rCell),""))

This gives me error:

The Xlookup returns the 5, but get get link part of the formula does not work or i might be doing something wrong.

 

[Alex Blakenburg]

Did you put the code for the function ExcelToDAX gave you in post #2 into a standard module and save your workbook as xlsm ?