Forum Discussion

PBIguy's avatar
PBIguy
Copper Contributor
Oct 18, 2021
Solved

Excel FILTER function to return hyperlinks

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 notic...
BI & Data Analysis
excel
Formulas and Functions
masterfab's avatar
masterfab
Copper Contributor
Feb 16, 2023

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 

masterfab's avatar
masterfab
Copper Contributor
Feb 16, 2023
From [Product Number] not in*
  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 17, 2023

    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. 

Resources