Links in vlookup

[erisaaka]

I've attached a sample document that I need help with.

On sheet 1, I have the 6 rows with data validation lists.

On sheet 2, I have the list along with hyperlinks.

On sheet 1, I would like to be able to select any one of the numbers in the list and for it to populate with the correlating hyperlink.

I've attempted to do this using vlookup but for some reason I am getting #N/A AND my hyperlink is missing.

Any help is very much appreciated.

Book1.xlsx

[sktneer]

Try this.......

Please Login or Register  to view this content.

and then copy down.

Hope that helps.

[erisaaka]

Thank you; however, this only fixes the N/A issues.

Is there a way to get the hyperlink from sheet 2 to display in column B on sheet 1?

[sktneer]

Try this.....

Please Login or Register  to view this content.

Is this what you want?

[erisaaka]

Not exactly.

Link 1 on sheet 2 results in google.com

Link 2 on sheet 2 results in yahoo.com

So on and so forth...

Using the above expression does not result in this behavior. In fact, it results in "cannot open the specified file"

I need the hyperlinks on sheet 2 to fully reflect, in look and behavior, on sheet 1 through the vlookup.

[perimidt]

Hi

Is it ok to add a helper column on sheet2?
If so I might have a UDF-solution.

Please Login or Register  to view this content.

C1 on sheet2 = FindHyperlink(B1)

And then just use vlookup on sheet1 to find the value in column C.

[erisaaka]

Thank you.

Not familiar with UDF. Can you help in guiding me how to implement this solution?

[perimidt]

Hi

Press Alt+F11.
Insert Module.
Paste everything in the grey area.

Now you should have the function FindHyperlink in the category User Defined when you press the insert function symbol (fx)

[erisaaka]

I pressed alt+f11, double clicked sheet 2 (because I can't paste anything into the grey area), pasted the code and closed the VBA.

I then pasted the following into cell C1 on sheet 2 =FindHyperlink(B1)

I get a #NAME? error.

[perimidt]

After Alt+F11 you have to choose Insert and then Module.
Insert Module.jpg

[erisaaka]

Got it to work.

Hmmm, my problem here is that I need it to still say "link 1", not google.com.

Any other ideas?

[perimidt]

Well, I suppose you could wrap the Hyperlink-function around the vlookup, like this:
=HYPERLINK(VLOOKUP(A2,Sheet2!A:C,3,FALSE),VLOOKUP(A2,Sheet2!A:C,2,FALSE))

[erisaaka]

Thank you!