Losing Cell Format In Lookup Function

[Jay]

I have a list (A) of 150 items (out of about 500 total items that
interchanges every day).

I have another list (B) of all 500 total items and their corresponding
categories (10 total categories). List B will never change. I have already
assigned all 500 items their category. Each category is a different color,
so all the cells in that column are 1 of 10 colors.

I need List A to also show the category of each item in the cell next to it.
In order for this to be easy to read, each category needs to be a different
color in this list as well.

MY PROBLEM: When I use a lookup function to assign the category to the item
in List A, the function only returns the value and NOT the format, which is
what I need as well.

Is there a way for the lookup function to return both the value and format
of the result vector?

 

[krcowen]

Jay

In Excel 2007 you can do this with conditional formatting. Excel <2007
is limited to 3 conditions for conditional formatting, which you can
expand a little using custom number format; but, I don't think you can
get to 10. I think if you don't have Excel 2007 you will need to
write some code to accomplish what you want.

Ken