How to compare values in two columns in an excel sheet and populate a value in another column

[aparicitudu]

Hi,

My requirements says to compare two i.e column A value with range of values and column B value with range of values and then populate column C with 1 or 0 based on the compare result.

A	B	C	D	E	F
NEW	RAHUL	1	0	0	0
PROGRESS	RAHUL	0	1	0	0
COMPLETED	RAHUL	0	0	1	0
NEW	RAVI	0	0	0	1

<tbody>
</tbody>

For Example:

A B C D
NEW RAHUL 1(IF (A="NEW" & B="RAHUL") 1 (IF (A="PROGRESS" & B="RAHUL").....
PROGRESS RAVI ...... ........
COMPLETED RAHUL ....... ........


Please help me out in finding a solution for this.

Thanks,
Aparichit.

 

[easy2understandexcel]

I have got to be honest with you... what you are asking is not very clear. Where do D E F come in? What determines value of 1 for C?

 

[aparicitudu]

Hi,
Sorry for being not clear.
Let me keep it in this way.
Column A can have values NEW, PROGRESS, COMPLETED.
Column B can have values RAHUL, RAVI
Column C can have two values either 0 or 1
when column A=NEW and column B=RAHUL, then C=value 1
For remaining cases column C should have value 0.

In the same way column D can have either 0 or 1 value.
value 1 only when column A=PROGRESS and column B= RAHUL
For remaining cases column D should have value 0.
The same way column E and F also as shown in above table.
I believe this will give a clear idea of my requirement.

Thanks,
Aparichit.

 

[antthorne]

Hi aparicitudu

If I'm understanding correctly if you put the following formula in column c:

=if(and(A1="NEW",B1="RAHUL"),1,0)

If you use the same method you should be able to complete the other columns

Hope I've understood what you meant!!!

Thanks
Ant

 

[norulen]

This should surely work for you.

Formula in Column C =IF(AND(A2="NEW",B2="RAHUL"),1,0)
Formula in Column D =IF(AND(A2="PROGRESS",B2="RAHUL"),1,0)
Formula in Column E =IF(AND(A2="COMPLETED",B2="RAHUL"),1,0)
Formula in Column F =IF(AND(A2="NEW",B2="RAVI"),1,0)

Exactly same as the solution provided by anthorne but this would save you some seconds of time and some stress on your brain :P

 

[aparicitudu]

Thank You Very Much.....