Excel 2003, SP2 win Win XP, SP2

Trying to use a VLookup to present user-selectable information from an
Excel range. The range contains large and small numbers, percents and
currency values in it - and VLookup doesn't seem to return the cell
number formatting of the "looked-up" cell.

I've kludged a way to get the formatting from the looked-up cell and
apply it to the cell doing the lookup, but what I'm doing converts the
looked-up cell's value to a text (I'm using the TEXT worksheet
function) - and that keeps me from doing summations, etc.

Is there any way to set a cell's number format with a combination of
built-in or user-defined worksheet functions that can co-exist with a
VLookup, HLookup or Match function?

Thanks in advance for any help!

James