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")



Grant wrote:

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...

"JulieD" wrote in message
...
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


"Grant" wrote in message
...
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





--

Dave Peterson