Clickable Hyperlink in an Array

[heroharold99]

Dear Excel gods and sages, I'm hoping to seek your advice -

I'm putting together a searchable database for my work and have managed to very inelegantly create a very crude search function with the following formula. There's a column (in red below) that contains links to other files as part of the searchable database. The idea is so that the user can click on the hyperlink and access the relevant files directly. But since this is an array, the hyperlinks are not clickable. Do you know of a way to get around that? Many thanks in time!!

=LET(f,FILTER(Table1,ISNUMBER(SEARCH(O2,Table1[Name of Company/Organization]))+ISNUMBER(SEARCH(O2,Table1[Case Lead Type]))+ISNUMBER(SEARCH(O2,Table1[Origin]))+ISNUMBER(SEARCH(O2,Table1[Year Founded]))+ISNUMBER(SEARCH(O2,Table1[Industry (primary)]))+ISNUMBER(SEARCH(O2,Table1[Industry (secondary)]))+ISNUMBER(SEARCH(O2,Table1[Industry (tertiary)]))+ISNUMBER(SEARCH(O2,Table1[Company Type]))+ISNUMBER(SEARCH(O2,Table1[Latest Revenue (millions USD)]))+ISNUMBER(SEARCH(O2,Table1[Revenue Type]))+ISNUMBER(SEARCH(O2,Table1[Lastest Valuation (millions USD)]))+ISNUMBER(SEARCH(O2,Table1[Profitable?]))+ISNUMBER(SEARCH(O2,Table1[Main Product/Service]))+ISNUMBER(SEARCH(O2,Table1[Key Innovation]))+ISNUMBER(SEARCH(O2,Table1[Company Contact]))+ISNUMBER(SEARCH(O2,Table1[Dilemma (primary)]))+ISNUMBER(SEARCH(O2,Table1[Dilemma (secondary)]))+ISNUMBER(SEARCH(O2,Table1[Topic (primary)]))+ISNUMBER(SEARCH(O2,Table1[Topic (secondary)]))+ISNUMBER(SEARCH(O2,Table1[HBS Unit (primary)]))+ISNUMBER(SEARCH(O2,Table1[HBS Unit (secondary)]))+ISNUMBER(SEARCH(O2,Table1[HBS Unit (tertiary)]))+ISNUMBER(SEARCH(O2,Table1[Status]))+ISNUMBER(SEARCH(O2,Table1[Link]))+ISNUMBER(SEARCH(O2,Table1[Remarks])),"No Records Found"),IF(f="","",f))

 

[bobsan42]

This has nothing to do with arrays. You cannot transfer hyperlinks like this. You can only transfer the value shown in a cell. So if the hyperlink has a TextToDisplay = "Click Here" then Click Here will be show in the cell and Click Here will be the value which the cell passes around.
To make a hyperlink you need the full path to a file instead of a hyperlink to it. Then you put the path in a HYPELINK() function and you get a clickable hyperlink.

 

[heroharold99]

This has nothing to do with arrays. You cannot transfer hyperlinks like this. You can only transfer the value shown in a cell. So if the hyperlink has a TextToDisplay = "Click Here" then Click Here will be show in the cell and Click Here will be the value which the cell passes around.
To make a hyperlink you need the full path to a file instead of a hyperlink to it. Then you put the path in a HYPELINK() function and you get a clickable hyperlink.

i see. thanks a lot for pointing me in the right direction! I managed to use iferror(hyperlink(lookup))) to extract clickable hyperlinks in the end.