Comparing 2 columns

  • Thread starter Thread starter lynn
  • Start date Start date
L

lynn

Column A is full of names (none repeating). Column B has
names as well, and the same name MIGHT already be in
column A. I want a formula in column C that will first
look at cell B1, then search all of Column A, and either
answer YES or NO if it is found. I though an If statement
would work, but it isnt. I wrote the following -

=IF(B1=(A1:A25),"Yes","No")

However, this is only returning NO as an answer, even if
the name in B1 IS located in the range of A1:A25. Am I
doing something wrong, or does this only work for values
and not text ? Using Excel 2000, SP2
 
lynn said:
Column A is full of names (none repeating). Column B has
names as well, and the same name MIGHT already be in
column A. I want a formula in column C that will first
look at cell B1, then search all of Column A, and either
answer YES or NO if it is found. I though an If statement
would work, but it isnt. I wrote the following -

=IF(B1=(A1:A25),"Yes","No")

However, this is only returning NO as an answer, even if
the name in B1 IS located in the range of A1:A25. Am I
doing something wrong, or does this only work for values
and not text ? Using Excel 2000, SP2
Click to expand...

=SUMPRODUCT((B1=A1:A25)*1)

This returns a 1 if the name in B1 exists in A1:A25, and returns a 0 if the
name does not exist. Put that in your IF statement to get...

=IF(=SUMPRODUCT((B1=A1:A25)*1)=1,"Yes","No")

Dave
dvt at psu dot edu
 
Lynn:

Try using a COUNTIF:



=IF(COUNTIF($A$1:$A$25,B1)=0,"No","Yes")



The "Yes" and "No" are reversed from the way you had it because the
COUNTIF=0 (meaning the value in B1 is not found in range A1:A25) will be the
TRUE condition for the IF statement.



HTH

Ed
 
Back
Top