if usage based on two ranges NO sum required
 

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?

if usage based on two ranges NO sum required
 

Try this:

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

HTH,
Elkar


"ross" wrote:

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?

if usage based on two ranges NO sum required
 

On Oct 30, 2:43 pm, ross wrote:
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!

if usage based on two ranges NO sum required
 

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

"ross" wrote:

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?

if usage based on two ranges NO sum required
 

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" wrote:

Try this:

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

HTH,
Elkar


"ross" wrote:

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?

if usage based on two ranges NO sum required
 

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" wrote:

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" wrote:

Try this:

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

HTH,
Elkar


"ross" wrote:

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?

if usage based on two ranges NO sum required
 

Excellent, that will help in many ways!

Many thanks. :-)

Ross A

"Elkar" wrote:

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" wrote:

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" wrote:

Try this:

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

HTH,
Elkar


"ross" wrote:

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?