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

Hi!

I need help in correcting a formula

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

but fails for HLOOKUP.

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

Please Login or Register  to view this content.

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