You can't. Formulas can only return values. You will need a macro to do this
use FIND to find the desired value and then use copy/paste SPECIAL.

--
Don Guillett
SalesAid Software

"Norm" wrote in message
...
when using the hlookup function how can I get the formating of the cell
located during the lookup to transfer to the destination cell.

thanks