Pull data as a string from a hyperlinked cell in Google Sheets

Question

I have utility that I've been working on (modified from an existing sheet) for a Tabletop game I run for my group.

Currently, it generates random items from a list. The name of each of the items has a corresponding hyperlink to the items description from the same sheet.

An editable copy of the generator may be found here.

My goal is to populate a new cell with with BOTH the item's name, and it's description.

So, if the generator returns an item name in cell "C4" with a hyperlink to its corresponding description cell, what formula should I use to populate this new cell (or cells) with both the name, and the descriptor?

Here is an image of the generator, with the hyperlink to the description cell on a different page.

Clicking the hyperlink routes you to the corresponding "Description" cell. [3

My ideal behavior, is for the formula to insert both the Name and the Descriptor into the new cell.

A visual example of the outcome I'm looking for can be found below.

Any advice or ideas would be appreciated.

Answer 1

I created a new sheet named marikamitsos and added 4 possible alternative solutions on cells I4:X4.

I believe one of them will suit your needs.

Solution 1
The first one uses an added helper column (M) on your Cyphers sheet. Using this formula we manage to have a single cell with a link as we had in this answer :

=vlookup(B4,Cyphers!A:Z,13,false)

The newly added Helper column has the following needed formula:

=ArrayFormula(HYPERLINK( 
                "#gid=0&range="&L2:L632,ArrayFormula(IF(LEN(J2:J632)<>0,K2:K632&CHAR(10)&CHAR(10)&J2:J632,""))))

Solution 2
For the second one we just put together the twice used VLOOKUP function which again produces a single cell but no link this time.

=vlookup(B4,Cyphers!A:L,2,false)&CHAR(10)&CHAR(10)&vlookup(B4,Cyphers!A:L,10,false)

Solution 3
On this third one we use the following formula which returns two separate fields one next to the other:

=TRANSPOSE(ArrayFormula(vlookup(B4,Cyphers!A:L,{2;10},false)))

Solution 4
Finally the fourth solution is very similar to the third one. The only difference is that this time the two fields are one under the other

=TRANSPOSE(ArrayFormula(vlookup(B4,Cyphers!A:L,{2,10},false)))

Pro Tip: Instead of having 4 formulas in cells C:F you can use a single one (look at my examples on the sheet):

=ArrayFormula(vlookup(B4,Cyphers!A:Z,{13,4,7,8},false))