Excel FILTER function to return hyperlinks

[PBIguy]

Hello,

I'm trying to create a tool that allows users to type in their company name, and it will return a link to which they can click on and navigate to.

I used the FILTER function, but notice that when the values are returned, the links are no longer there.

I think the problem is that FILTER function doesn't seem to return links. Is there a way around this?

I included a workbook here, and the first tab is the tab where users type in company A, B or C and the FILTER function returns the company name and a link. but the link doesn't seem to include anything when returned.

thanks!

[#2 · Accepted solution]

@PBIguy 

I'm afraid there is no workaround at the moment. The FILTER function only returns the value of the filtered cells, not any hyperlinks in those cells.

See Makeing a filter with hyperlink and can't press the hyperlink.  (Sadly, Excel UserVoice has been discontinued since then)

[#3]

Thank you Hans for looking into it.

That really sucks, It would be a great feature for the FILTER function.

[#4]

@PBIguy 

Since you have the addresses within HYPERLINK formulas I believe there might be a workaround in this case using FORMULATEXT...

[#5]

@lori_m 

Great idea!

[#6]

nicely done @lori_m
hope you don't mind I added it to my cheat-sheet of useful functions/formulas

[#7]

im having the exact same issue with my sheets. I've tried to use xlookup and vlookup to avail.  Can you help me out? 

This is My current formula. 

I'd like it to return the hyperlinks in [#product numbers]

=FILTER(IF(ISBLANK(Table1), "", Table1),ISNUMBER(SEARCH(H2,Table1[[#Data],[Product Number]])) + ISNUMBER(SEARCH(H2,Table1[[#Data],[Product Name]])), "Nothing Found")

Any help is greatly appreciated  

 @mtarler 

[#8]

From [Product Number] not in*

[#9]

@masterfab  so it is hard without seeing what your data/sheet looks like.  Your formula:

=FILTER(
   IF(ISBLANK(Table1), "", Table1),
   ISNUMBER(SEARCH(H2,Table1[[#Data],[Product Number]])) + 
   ISNUMBER(SEARCH(H2,Table1[[#Data],[Product Name]])), 
   "Nothing Found")

Appears to FILTER our ROWS of Table1 based on H2 being found in EITHER [Product Number] OR [Product Name] columns

but you say you are looking for the hyperlink in [Product Number]?

BTW, there are 2 ways hyperlinks can be in excel: a) using the HYPERLINK() function or b) directly embedded.  directly embedded can be directly pasted or imported or inserted but basically you do not see the hyperlink path when you edit the formula.  The trick suggested by Lori only works for type a) where the HYPERLINK() function is used and therefore using the FORMULATEXT() function you can back out the that includes the hyperlink and using some text manipulation pull that hyperlink out.

So a) not sure what your data/sheet looks like and what you need and b) not sure if you can even use this trick or if you hyperlinks are embedded.