Compare cell values

  • Thread starter Thread starter Grant
  • Start date Start date
G

Grant

I have a number of cells, each containing a different string value. Is there
a way for me to determine whether a string from position A1 in my sheet,
exists anywhere else in that same column on my sheet.

ie does text from Cell A1 match any text between Cell A2 and Cell A100?

Thanks,
Grant
 
Hi Grant

one option
=IF(ISNUMBER(MATCH(A1,A2:A100,0)),"found","not found")

or
=IF(COUNTIF(A2:A100,A1)>=1,"found","not found")

- or if you want to return the row number of where it is found just use
MATCH(A1,A2:A100,0)+1

Hope this helps
Cheers
JuileD
 
Excellent thanks Juile! Just one question, is there a way to use the countif
formula to search every row from A1 to A100 excluding the current row? Your
formula COUNTIF(A2:A100,A1) works great but when I drag it down the column
it searches from that point on and not any previous cells.

So is there a way to do search every row from A1:A100 except for the current
row?
Something like =COUNTIF( IF ( Current cell not = A1 then use the array(
$A2:$A100 )), A1)

or something similar? I hope Im making sense...
 
Grant wrote...
....
So is there a way to do search every row from A1:A100 except for the current
row?
Click to expand...
....

If you mean the formula in, say, A23 would check for instances of the
value of A23 in A1:A22 and A24:A100, then try

=IF(SUMPRODUCT((A$1:A$100=A23)*(ROW(A$1:A$100)<>ROW(A23)),"found","not
found")

Alternatively, you could use

A1:
=IF(COUNTIF(A2:A100,A1),"found","not found")

A2 (then filled into A3:A99):
=IF(COUNTIF(A$1:A1,A2)+COUNTIF(A3:A$100,A2),"found","not found")

A100:
IF(COUNTIF(A1:A99,A100),"found","not found")
 
Or maybe you could just check to see if the results were >1 (instead of >=1).

=if(countif($a$1:$a$100,a1)>1,"Found somewhere else besides this cell","Nope")
 
Thanks thats perfect! I think I remember trying something similar but
couldnt get it to work.
 
Back
Top