Hyperlink Match

[Guest]

I am trying to match two columns and provide a link in the third column.

Data in Worksheet1
Column H I J
Description Sub Amount
Product 1 100 25000
Product 2 200 40000

Worksheet2
Description Sub Details
Product 1 100 stuff
Product 1 200 stuff
Product 1 100 more stuff
Product 2 200 stuff
Product 2 100 more stuff

In Column G in Sheet1, I have this formula, HYPERLINK("#'Sheet2'!A" & IF
(MATCH(H12,Sheet2!$A$1:$A$29,0)=MATCH(I12,
Sheet2!$A$1:$A$29,0),MATCH(H12,Sheet2!$A$1:$A$29,0),0), "Link")

The formula will work only if the first row of data is correct. Could
someone help me fine tune this please?

Thanks, Corrine

 

[Guest]

One way to get there ..

Illustrated in this sample:
http://www.flypicture.com/download/Njk5NQ==
Hyperlink n dual criteria match.xls

Put in G2, then array-enter the formula by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER
=HYPERLINK("#"&CELL("address",INDIRECT("'Sheet2'!C"&MATCH(1,(Sheet2!A$2:A$29=H2)*(Sheet2!B$2:B$29=I2),0)+1)),H2&"-"&I2)
Copy G2 down as far as required

The above creates hyperlinks in col G which jumps you to the row in
"Details" col C in Sheet1 which matches with the "Product#" & "Sub" (a dual
criteria match). Note that unique "Product#" & "Sub" combos are assumed
within Sheet2's cols A and B, otherwise the hyperlinks can only direct you to
the first matched row in Sheet2.

 

[Guest]

Oops, typo correction, line:

.. jumps you to the row in "Details" col C in Sheet1 ..

should read:

.. jumps you to the row in "Details" col C in Sheet2 ..

Put in G2, ...

The hyperlink formula is placed in Sheet1's G2

---

 

[Guest]

Thank you - I will try that. Corrine

One way to get there ..

Illustrated in this sample:
http://www.flypicture.com/download/Njk5NQ==
Hyperlink n dual criteria match.xls

Put in G2, then array-enter the formula by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER:
=HYPERLINK("#"&CELL("address",INDIRECT("'Sheet2'!C"&MATCH(1,(Sheet2!A$2:A$29=H2)*(Sheet2!B$2:B$29=I2),0)+1)),H2&"-"&I2)
Copy G2 down as far as required

The above creates hyperlinks in col G which jumps you to the row in
"Details" col C in Sheet1 which matches with the "Product#" & "Sub" (a dual
criteria match). Note that unique "Product#" & "Sub" combos are assumed
within Sheet2's cols A and B, otherwise the hyperlinks can only direct you to
the first matched row in Sheet2.

 

[Max]

welcome ..