if usage based on two ranges NO sum required

[ross]

COL_A COL_B COL_C
2 1 Yes
2 1 Yes
2 2 Yes
1 3 No
I need to have COL_C say yes if any value in COL_B = any value in COL_A.
I tried the following: =if($A$2:$A$5=B2, "yes", "no") . . . didn't work.
I read the formula as "if range a2 to a5 = B2, then say yes. . .

Any ideas?

 

[Elkar]

Try this:

=IF(COUNTIF($A$2:$A$5,B2)>0,"Yes","No")

HTH,
Elkar

 

[domenic22]

COL_A COL_B COL_C
2 1 Yes
2 1 Yes
2 2 Yes
1 3 No
I need to have COL_C say yes if any value in COL_B = any value in COL_A.
I tried the following: =if($A$2:$A$5=B2, "yes", "no") . . . didn't work.
I read the formula as "if range a2 to a5 = B2, then say yes. . .

Any ideas?

Try...

C2, copied down:

=IF(ISNUMBER(MATCH(B2,$A$2:$A$5,0)),"Yes","No")

Hope this helps!

 

[Teethless mama]

=IF(COUNTIF($A$2:$A$5,B2),"yes","no")

 

[ross]

Thanks, works great.

How do I read this? I see the range ref and check against B2. . . then???

I figure if I understand what it says, I can move towards my own excel
solutions.

Ross

 

[Elkar]

The COUNTIF function takes each value within the range (A2:A5) and checks to
see if the condition is met (=B2). If so, it increments (or counts) by 1.
So basically:

Is A2 = B2?
Is A3 = B2?
Is A4 = B2?
Is A5 = B2?

For each of the values that are true, COUNTIF would increment by 1 and
return that value (in this example, it would be a number from 0 to 4).

The IF portion then takes the result of the COUNTIF and checks to see if it
is greater than 0. If so, "Yes" is returned. If not, "No" is returned.

Does that help?
Elkar

 

[ross]

Excellent, that will help in many ways!

Many thanks.

Ross A