Comparing two colunms
 

I have a user that is trying to do the following:

In column A is a set of numbers (for this example we will say 40 rows)
In column B is a set of numbers (for this example we will say 40 rows)
In column C she waits to write a forumla that looks looks at the numbers in
column A and compares them to the numbers in column B. If it looks at the
numbers in A1 for example and there is a matching number in B7 it would be a
true value in A3, if no matching number was found it would put a false in A3.
This forumla would continue for all 40 rows. What I had tried was
=IF(A1=$B$1:$B$40,1,0)

Comparing two colunms
 

The formula =COUNTIF($B$1:$B$B40,A1) will count how many times the value in
A1 is also found in B1:B40
So =IF(COUNTIF($B$1:$B$40,A1),1,0) will return 1 or 0 (note that Excel
treats any non-zero number as TRUE)
The formula =COUNTIF($B$1:$B$40,A1)0 will return TRUE or FALSE
and --(COUNTIF($B$1:$B$40,A1)0) will return 1 or 0

Your reference to A3 seems to be a typo?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Red Deer Tech" <Red Deer wrote in message
...
I have a user that is trying to do the following:

In column A is a set of numbers (for this example we will say 40 rows)
In column B is a set of numbers (for this example we will say 40 rows)
In column C she waits to write a forumla that looks looks at the numbers
in
column A and compares them to the numbers in column B. If it looks at the
numbers in A1 for example and there is a matching number in B7 it would be
a
true value in A3, if no matching number was found it would put a false in
A3.
This forumla would continue for all 40 rows. What I had tried was
=IF(A1=$B$1:$B$40,1,0)