Column Matching and Colouring (VLookUp variation?)

Hi fellow forum members!

You are going to be sick of this question, and I have come across variations of it on many posts, but never one which exactly meets my requirements.

I start with three columns which consists of are NYSE Stocks: Russell 1000, Russell 2000, Russell 3000.

The Russell 1000 and Russell 2000 have no overlaps. On the other hand, the Russell 3000 contains the entire 1000 and the 2000 (and some additional stocks).

The background cell colour of the Russell 1000 is in GREEN, and the Russell 2000 is in YELLOW.

What I would like is for the cells in the Russell 3000 to show up GREEN if it also belongs to the Russell 1000, and YELLOW if it also belongs to the Russell 2000. Alternatively, if you don't know how to do the colouring, a fourth column which I shall place next to the Russell 3000, can return a value of "1000", or "2000" as a means of indication is also acceptable.

The reason why I wish to do this is because I will classify the Russell 3000 into various industry groups. I would like to have a visual indication of a stock's capitalisation (based on its membership of Russell 1000 or Russell 2000). I shall repeat the same procedure for other exchanges.

Thank you so much for your help. Been looking for this all over the web.

if the russell 1000 are in column A
Russeld 2000 in column B

Russel 3000 in column C

Select all the cells you wish to apply the format to. Go format_conditional formating, formula is

If the first cell is C2
countif(a:A,c2)>0 format to Green add a new condition
counitif(b:B,C2)>)format to Yellow


Regards

Dav

Dear Dav,

Thanks for your reply. It worked exactly as you have mentioned. One more thing. How do I apply this conditional formula to other cells?

Column "C" is not actually a single column, but all over the place, since it is split into industry types. How do I "copy and paste" this conditional format to other cells? I don't want to have to manually do the conditional formatting for each and every industry groups.

Don't forget, I have to do many different exchanges too, so it'd be a process that would be repeated 50-100 times.

Thanks again.

People can only answer the question asked. We have no idea about the shape of your spreadsheet or where the data is stored on it. So it is a little difficlut to give you an answer.

If column A and B are fixed

countif($a:$A,c2)>0 format to Green add a new condition
counitif($b:$B,C2)>)format to Yellow

as the format, which can then be paste special as formats to the other cells you wish to apply it to

If not you will have to post some data, you can zip an excel file and attach it

Regards

DAv

Dear Dave,

Thank you for your reply. No, you quite misunderstood me. Your first reply worked very well. My second post is actually a SECOND question, which expanded on the first.

Anyway you second reply has helped answer my second question. SPECIAL PASTE did the trick.

Thank you for your help. All is well.

I knew it answered the question, but the solution for the second bit was a bit of a guess, as I did not know how your data was formatted. Glad it worked

Guesses often fail

Thanks for the feedback

Regards

Dav