Help with using IF to Search for text and create hyperlink.

**danielball** · 04-28-2014, 11:20 AM

Hello,

I am struggling - I basically need a formula in one tab of a spreadsheet that if another cell contains text it will search for that text in another tab and display a useable hyperlink to it.

So I want a formula in A1 in Sheet1 that will search for the text contained in A2 in Sheet2 and display a useable hyperlink to the cell in which that text is contained, in Sheet2.

This may not even be possible, but if it is then that would save me SO much time.

Thanks in advance for your help guys.

Daniel B

**bebo021999** · 04-28-2014, 11:32 AM

Maybe
=IF(ISNUMBER(SEARCH("YourText",Sheet2!A2)),HYPERLINK(Sheet2!A2,"Click"),"")
Returns hyperlink name "Click" if sheet2!A2 contents, i.e, "YourTextHere",...
returns blank if not.

**danielball** · 04-28-2014, 11:48 AM

Hi. Thanks for the reply.

That was initially what I was attempting to use - I think this is an instance where grammar is very important! Misunderstanding I think:

What I meant is if cell A2 in Sheet1 contains "YourTextHere" I want the formulae in A1 in Sheet1 to search ALL of Sheet2 for the text, and if it finds the text give me a hyperlink to the cell in which that text is contained IN Sheet2. Do you see what I mean?

Thanks for the help though, I can see where my post needed clarity!

Daniel

**bebo021999** · 04-28-2014, 11:54 AM

What happened if searching ALL CELLS of Sheet2? Does it have limitation range in sheet 2, i.e, A1:AA100?

**martindwilson** · 04-28-2014, 12:17 PM

=HYPERLINK("#sheet2!"&ADDRESS(SUMPRODUCT((Sheet2!A1:Z1000=B1)*ROW(Sheet2!A1:Z1000)),SUMPRODUCT((Sheet2!A1:Z1000=B1)*COLUMN(Sheet2!A1:Z1000)),4),"clickhere") will hyperlink to the cell where the text entered in b1 is found in sheet 2 range a1:z1000

**bebo021999** · 04-28-2014, 12:18 PM

Assuming your searching range in sheet2 is A1:O20
cell A2 in Sheet1 contains "YourText"
This formula set in A1 will return blank if not found, if found, display HYPERLINK, name "Click" and lead cursor to the relevan cell in sheet 2 that contains "YourText"
A1:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.