Need help to correct an ADDRESS & MATCH formula for Hyperlinking hlookup result

Hi!

I need help in correcting a formula

 =HYPERLINK("#"&ADDRESS(MATCH(D2,Sheet3!$A$1:$Z$1,0),2,,,"sheet3"),HLOOKUP(D2,Sheet3!$A$1:$Z$2,2,FALSE))

that gives the result of a hlookup and also hyperlinks so that I can go directly to the result cell.

The formula works correctly for results that come from VLOOKUP

 =HYPERLINK("#"&ADDRESS(MATCH(A2,Sheet2!$A$1:$A$180,0),2,,,"sheet2"),VLOOKUP(A2,Sheet2!$A$1:$B$242,2,FALSE))

but fails for HLOOKUP.

On analysis I found that the formula for ADDRESS & MATCH combination

 =ADDRESS(MATCH(D2,Sheet3!$A$1:$Z$1,0),2,,,)

is probably the culprit that is pointing to the correct distance in vertical direction but vertically even for hlookup function.

Any help in identifying as to what am I missing here, so that it works for HLOOKUP also?

SAMPLE FILE ATTACHED

Regards,

Naira

The syntax of the Address function is

=ADDRESS(ROW#,COLUMN#,abs,a1,sheettext)

You seem to have the Row and Column reversed.

Originally Posted by Jonmo1

The syntax of the Address function is

=ADDRESS(ROW#,COLUMN#,abs,a1,sheettext)

You seem to have the Row and Column reversed.

Hi!
I know the syntax but I still cant get it to work.
Can someone please give the exact formula or modify it in the attached worksheet?

I'm not sure of that..

=ADDRESS(MATCH(D2,Sheet3!$A$1:$Z$1,0),2,,,)

What is the MATCH function doing? Finding the COLUMN Number where D2 exists in A1:Z1.
But you put that in the ROW# part of the Address function.

So you probably want
=ADDRESS(2,MATCH(D2,Sheet3!$A$1:$Z$1,0),,,)

Originally Posted by Jonmo1

So you probably want
=ADDRESS(2,MATCH(D2,Sheet3!$A$1:$Z$1,0),,,)

PERFECT!!!

Thanks it works now

You're welcome