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

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.

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.

• Please read how to and share a test sheet so as you can be easier helped. Feb 7, 2020 at 17:33
• Will do. Thank you for the input. Feb 9, 2020 at 0:15

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))``