# Compare two cells with different values in Excel

So I have two columns:

1. Values can be "Yes - Start", "Yes - End", "Yes - Both" and "No"
2. Values are the same as column 1

They don't match all the time, so I want to create an additional column that would say:

• "Rare", if cell in column 2 is different from "No"
• "Common", if cell in column 2 is equal to "No"
• "C&R", if cells from both columns (A1 and B1, for e.g.) have a value different from "No"

I tried

``````=IF(B1="No";"Common";"Rare")
``````

But this only solves the first two points, the "C&R" one I don't know how to solve.

-

If I've understood this right, then you could use the following formula:

``````=IF(B1="No","Common",IF(AND(A1<>"No",B1<>"No"),"C&R","Rare"))
``````

It basically breaks down to:

• If B1 is no then it's Common
• If B1 is one of the yes values then it's C&R if both A1 and B1 are yes values, otherwise B1 is Rare.

For your additional requirement in the comments of returning "None" if both A1 and B1 are no then change the formula to:

``````=IF(B1="No",(IF(A1="No","None","Common")),IF(AND(A1<>"No",B1<>"No"),"C&R","Rare"))
``````

This breaks down to:

• If B1 is no then it's Common if A1 is one of the yes values, otherwise it's None
• If B1 is one of the yes values then it's C&R if both A1 and B1 are yes values, otherwise B1 is Rare.
-
Thanks! This does it, although I noticed something now.. when both A1 and B1 are "no", the result should be "None", or else it will look odd. How can I do that? Also, what do <> mean? Thank again for your time! – Santiago Apr 20 '11 at 14:40
@Santiago <> means "not equal to". I'll edit my answer in a second to add in your latest requirement. – 8088 Apr 20 '11 at 14:51
thanks you so much! this does it! I'll study the formula well so next time I'll be good on my own. – Santiago Apr 20 '11 at 15:04

this should do it:

``````=IF(AND(A1<>"No",B1<>"No"), "C&R",IF(B1="No", "Common", "Rare"))
``````

This basically says -

``````if both columns aren't "No", then "C&R" // your last case
else
if second column is "No", then "Common" // your 2nd case
else "Rare" // i.e., it's not "No"   // your first case
``````
-