Edited following OP's request:
##How to create no pop-up Hyperlinks/Bookmarks for an array of cells within the same tab/sheet
=ArrayFormula(HYPERLINK(
"#gid=1369991651&range="&A2:A,IF(LEN(B2:B)<>0,B2:B,"")))
You will need a helper column placed anywhere within the same tab and in this column you will reference the cells of your "Descriptions" column. You can then, even hide the "HELPER" column.
Notice that because we reference an array within the same tab/sheet we do NOT need the sheet ID. Instead we use the # character.
The created hyperlinks behave like HTML "anchors" and do not produce any pop-up windows.
Original answer:
Assuming your text/title is in cell B2 the formula you need is:
=HYPERLINK("http://www.google.com/";""&B2&"")
If you want to use an ARRAYFORMULA you would change the above formula to:
=ArrayFormula(HYPERLINK("http://www.google.com/";""&B2:B4&""))
When you want to have the formula link different cell titles to consecutive link cells (of the same row or not) you can use:
=ArrayFormula(HYPERLINK((""&C21:C24);B21:B24))