Google Sheets – Create Link in Cell Using URL from Another Cell

google sheets

On one worksheet I have a table of data that includes an item name, person name, price, and a URL.

On an overview worksheet in question I use the FILTER formula to list the items by person using the following formula: =FILTER(Data!A2:A14,Data!B2:B14=D2). I would like to have this cell also have be hyperlink to the item (if there is a URL in the found row).

Is this possible without a script? (With enough scripting work I'm sure I can do whatever I want but that feels like the hard way)

EDIT

I have discovered the HYPERLINK function which seems to be the key but I cannot get it to work.

At first I put the HYPERLINK function on the inside of my filter like this:
=FILTER(HYPERLINK(Data!D2:D40, Data!A2:A40),Data!B2:B40=D2)

However this gave this message: error: Mismatched range lengths

Then I thought to combine the URL and text after the filter like so:
=HYPERLINK(FILTER(Data!D2:D40,Data!B2:B40=D2),FILTER(Data!A2:A40,Data!B2:B40=D2))

Which worked for the first value in the list but any further cells (that use CONTINUE) are empty.

EDIT 2

I was hesitant to do this at first but here is a link to my spreadsheet. Just don't tell my family what I'm getting them lol. I went ahead and made a copy so don't worry about messing it up. You can see my attempt at it along a manually created example of what I want it to look like.

Best Answer

You were so close !!!!!!!!

Use the following formula.

Formula

=ARRAYFORMULA(
   HYPERLINK(
     FILTER(Data!D2:D40,Data!B2:B40=D2),
     FILTER(Data!A2:A40,Data!B2:B40=D2)
   )
 )

Explained

The ARRAYFORMULA will take on the ranges, set in the filter, and return the matches.

Related Topic