Formatting the results of a lookup..

[Paddy]

Here is the problem. A lookup can have the following results 1) Text
2) Number or 3) Percentage. The lookup works fine and when the
results are text and numbers everyones' happy. but when the result is
a percentage it appears as eg 0.005

I have tried conditional format and preformatting the columns but
nothiing seems to work.

More Detail

Data
Product Code State
Prod1 Available (Result of Lookup = Available)
Prod1 123 (Result of Lookup = 123)
Prod1 25% (Result of Lookup = 0.25 but I need it to
say 25%)

Hope someone can help. Thanks in advance

 

[Peo Sjoblom]

This is a case of "a not so good spreadsheet design", don't mix different
types of item in the same column/row if you need them to be different. Now
when it comes to you suggestion

=CELL("format",INDEX($C$2:$C$5,MATCH(A1,$B$2:$B$5,0)))

this would return a format to the equivalent of

=VLOOKUP(A1,$B$2:$C$5,2,0)

so you are suggesting

=IF(CELL("format",INDEX($C$2:$C$5,MATCH(A1,$B$2:$B$5,0)))="P0",TEXT(VLOOKUP(
A1,$B$2:$C$5,2,0),"0%"),VLOOKUP(A1,$B$2:$C$5,2,0))

I believe that using the format part of the cell function is very volatile,
all it takes is a change from
0% format to 0.00% format and it will return decimal values so you would
have to use IF(OR etc
and the formula will soon grow to big, besides a change of format could
happen so easy
I'd suggest different layout, don't mix text, percentage/other numbers in
the same range if you need
to look up values.



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

This may be a little heavy weight, but if you use a lookup that returnes a

reference to the cell instead of a value (e.g. INDIRECT), you could do
something like:

 

[Paddy]

Many thanks for all your help. I was expecting something really simple
and obvious that I had missed. However, I will try some of these.
There is certainly enough 'food for thought' to be getting on with.

Thanks again

Paddy