There's no way to set a cell format with a function.

There is a workaround to what you are doing, though. Lets say your range of
returned values is B1:B100 (the results of the VLOOKUP's). Whenever you call
that range for a calculation, included it within the VALUE() function. For
example:

=SUM(VALUE(B1:B100))

And then commit it with CTRL+SHIFT+ENTER as it is an array function. This
way you can still do calculations on the text values in this range.

--
Regards,
Dave


" wrote:

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