Hyperlink Match

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Thank you - I will try that. Corrine

Max said:
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.
Click to expand...
 
Back
Top