VLOOKUP with Hyperlink
Posted by Connie on September 24, 2001 11:23 AM
I have a cell where the user picks a brand name (A6) from a drop-down list made with data validation. Another cell, (A7) contains a drop-down list of model numbers. I'd like the user to be able to hyperlink to a text document that pertains to the choices they've made so they can read more about the brand and model they picked.
Currently I have Table1 which has all brand names in column A, all model numbers in column B, and the path for the hyperlink to that particular document in column C.
My formula (this is where I'm stuck) is:
=HYPERLINK(VLOOKUP(Brand,Table1,MATCH(Model,3,0),0))
where "3" represents the 3rd column where the hyperlink paths occur. I think I've got the right ingredients, but I know they are mixed up wrong. Can someone help?
Thanks,
Connie
Posted by Aladin Akyurek on September 24, 2001 11:28 AM
Connie,
That 3 in MATCH must be a range, so replace 3 by the real column range.
Aladin
=======
Posted by Connie on September 24, 2001 12:02 PM
Aladin,
Thanks for the tip. Unfortunately, I'm still coming up with "#N/A". Could this be indicative of a different type of problem? I tried specifying the range for MATCH in a couple of different ways, such as C1:C5 and then I actually gave that portion of column C its own name, "links" and used that in the MATCH formula, but I continue to get the same "#N/A" message.
Further thoughts?
Thanks!
Posted by Aladin Akyurek on September 24, 2001 12:15 PM
Upon further thought...
I believe you need something along the lines of what follows, because I suspect you have more than one model corresponding to a brand and accordingly a different path for each brand+model combination.
=HYPERLINK(INDEX(range-of-path-values,SUMPRODUCT(MATCH(A6&A7,range-of-brand-values&range-of-model-values,0))))
The ranges mentioned are to be found in your Table1.
If this is not the case, you might want to send me a copy of your workbook.
Cheers,
Aladin
Posted by Connie on September 24, 2001 12:53 PM
Aladin, that worked perfectly! Thank You!! (n/t)
