Make the result of a HLookup function presented as a hyperlink

[Berenloper]

Hi everyone,

Is it possible to make the result of a HLookup function presented as a hyperlink?

I have these two formulas:
=HYPERLINK("[Hyperlink.xlsx]Sheet1!D2",D2) (gives a working link on the first sheet)
=HLOOKUP(D2;Sheet2!1:1,1) (gives the plain result off the second sheet)

The HLookup presents the correct result, but I want to make it clicable to jump right to it.

I thought of joining it with the hyperlink function, but I can't get the syntax of it right.
Is it therefore even possible to combine them?

Best regards,
Berenloper

 

[Jeffrey Mahoney]

I don't know what is in the Lookup range. I'm assuming that cell D2 is a number for a column, so in this case I would use index. See if this can help

Book9988.xlsm
	A	B
1	Col	Hyperlink
2	1	[Book9988.xlsm]Sheet1!$A$1
3	2	[Book9988.xlsm]Sheet1!$B$2
Sheet9

Cell Formulas
Range	Formula
B2:B3	B2	=HYPERLINK(INDEX(Sheet10!$1:$1,1,A2))

Book9988.xlsm
	A	B
1	[Book9988.xlsm]Sheet1!$A$1	[Book9988.xlsm]Sheet1!$B$2
Sheet10

Cell Formulas
Range	Formula
A1	A1	=CELL("address",Sheet1!A1)
B1	B1	=CELL("address",Sheet1!B2)

 

[Berenloper]

Hi Jeffrey,

Thanks for replying!
I will have a look at it and let you know what the result is.

Best regards,
Berenloper

 

[Berenloper]

Hi Jeffrey (and others),

I tried to use your suggestion about Index, but no luck. Maybe my questing was not clear enough.
So, let's try again.

Check these images:

Sheet1, cell B3 contains a (Dutch) HLookup which returns the result 'beer' from Sheet2.
What I'm looking for is a hyperlink in this cell (B3) based on the HLookup formula, so I can jump directly to cell B1 in Sheet2.

Hope this helps.

Regards,
Berenloper

 

[Jeffrey Mahoney]

In cell B3 enter this formula
=HYPERLINK(CELL("address",Sheet2!B1),Sheet2!B1)

 

[Berenloper]

Hi Jeffrey,

Thanks, your hyperlink formula works, but it's static.
The target "beer" can everytime be anywhere on row 1 in Sheet2. Now it's on B1 but a next time it can be on Z1.
Therefore a need some HLookup to get the actual adress where the hyperlink should refer to...

I did some experimenting like =HYPERLINK("[Hyperlink.xlsx]Sheet1!D2";HLookup(D2;Sheet2!1:1;1)) but it fails.

So, if possible, what's the right order to use Hyperlink with a Hlookup?

 

[FormR]

Hi, I don't think the HLOOKUP() function is appropriate in this situation, but here's one alternative you could try:

=HYPERLINK("#"&ADDRESS(1,MATCH(A2,Sheet2!$1:$1,0),,,"Sheet2"),A2)

 

[Berenloper]

Hello FormR,

Yes! That works perfect!! Thanks so much.
What a nice approach with this match function.

This will make my work a lot easier. Have a nice day!

Regards,
Berenloper