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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Corrine" wrote:
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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you - I will try that. Corrine

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Corrine" wrote:
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

welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Corrine" wrote in message
...
Thank you - I will try that. Corrine