Index Match Formula to return the cell's "Friendly Name" AND the hyperlink behind it

**GoGators** · 12-06-2012, 12:41 PM

Hello,

First time post. Thank you in advance.

I'm currently using an Index Match formula to return specific topics based on a client name. Those topics could be their location, contact name, website etc. My problem is that in my reference sheet I have the website hyperlinked, but with a "friendly name" displayed. When I Index Match the customers website it only brings the "friendly name" and not the hyperlink behind it.

Is there a way to have a formula return the "Friendly Name" AND have the hyperlink still work?

I do not want to display the actual website since in many cases, the website is very long.

Thank You

**dilipandey** · 12-06-2012, 01:46 PM

Hi GoGators,

Welcome to the forum.
I guess you already answered this

I do not want to display the actual website since in many cases, the website is very long.

So this is why even hyperlink formula has a syntax part as "friendly name" because website address can be very long

Thus, a formula can fetch whatever a cell has in its display, not anything which is there in the formula like.. if a cell has formula =sum(5+7), you can get 12 out of it and not "sum" , "5" or "7" though there are some ways to do so as well and one of them to use a defined name using Get.Cell function. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**GoGators** · 12-06-2012, 02:49 PM

Thank you DILIPandey for the quick response.

I am confused by your answer though. You say that a formula cannot return the content of the cell, but then you said there is a way possibly using Get.cell function.

If there is a way could you please explain. Thank you.

**dilipandey** · 12-07-2012, 04:45 AM

Hi GoGators,

okay.. use the below approach

Create a below defined name :-

Formula:

Please Login or Register  to view this content.

Where A1 is the cell having hyperlink

Now obtain the link out from it, using below formula:-

Formula:

Please Login or Register  to view this content.

See attached:- getting link out.xlsm

Regards,
DILIPandey

<click on below 'star' if this helps>

Index Match Formula to return the cell's "Friendly Name" AND the hyperlink behind it

LinkBack

Thread Tools

Rate This Thread

Display

Index Match Formula to return the cell's "Friendly Name" AND the hyperlink behind it

Re: Index Match Formula to return the cell's "Friendly Name" AND the hyperlink behind it

Re: Index Match Formula to return the cell's "Friendly Name" AND the hyperlink behind it

Re: Index Match Formula to return the cell's "Friendly Name" AND the hyperlink behind it

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions