Forcing vlookup to return formula instead of result

[USgA0HwU1P]

Sheet1 is vendor number and vendor name. Vendor name takes the form of a hyperlink formula that links to their homepage. (i.e. so the text that displays is Microsoft but if you were to actually click it, it takes you to microsoft dot com)

Sheet2 contains the vendor number.

The goal is to do a vlookup that populates sheet2 with the vendor name but retains the hyperlink.

What's the best way to do this? Thanks.

 

[Special-K99]

Ok, so to put things more clearly you want to do a VLOOKUP on a vendor number and pull back the hyperlink text and the actual URL, presumably in separate cells?
Doesn't look like you can get the URL with a formula, only option that comes up on Google is via VBA/UDF.