if usage based on two ranges NO sum required

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
Click to expand...

Try...

C2, copied down:

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

Hope this helps!
 
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
 
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
 
Back
Top