Vlookup to return hyperlinked address

[Grizlore]

Dear All

I need help to Vlookup and return a hyperlinked address, if this is possibly (?)

On sheet1 I have two columns; one with an ID number, the second column needs to be a hyperlink to a file.

On sheet2, I have one column, which contains a hyperlink to files with the same name as the ID numbers in sheet1, column1

For example…

The ID numbers in Sheet1, column 1 could be...

ABC1-1
AABB10-2
BGF9-2

On sheet 2 column1, there will be a list of hyperlinked names which start with the same ID number, however… the file types will be different, so an easier concatenated type hyperlink can’t be made,

Example of sheet 2 column1
ABC1-1.doc
AABB10-2.pdf
BGF9-2.tif
BGF5-2.tif
BGF4-2.tif

I was thinking that a Vlookup would be the best way to find the matching items, however…

a) Can a Vlookup find the first part of the ID number, i.e. Could you search for ABC1-1, and it find ABC1-1.jpg (i.e. stop at the file extension, lengths vary)

b) Can a Vlookup return the hyperlink in the cell it finds?

Not sure this is possible, OR if this is the best way to do this.

Any help would be appreciated

 

[Andrew Poulsom]

Try:

=HYPERLINK(VLOOKUP(A1&"*",Sheet2!A:A,1,FALSE))

 

[Grizlore]

Thanks Andrew,

I had to put in the generic files location in, but after that... works like a dream.


=HYPERLINK("\\WWW0\WWWSHARE\CofAs\"& VLOOKUP(A2&"*",'All the CofAs'!A:A,1,FALSE),"view")


Many thanks for your help!


Regards