Finding matches in two columns
 

I have two columns of years. Column A has 27 years from 1911 to 2007
indicating a particular year a house was built. Column B has all years from
1800 to 2009 use to plot a graph. In column C I want to find if any of the
years from 1800 through 2009 matchs one of the 27 years in column A and
return a 1 if that year matches or a 0 if it does not. With the results in
column C I can graph all years on the x axis and which one of them had a
house built.
Erik (a Word and Excel 2007 user)

Finding matches in two columns
 

One way using COUNTIF
In C2: =IF(B2="","",IF(COUNTIF(A:A,B2),1,0))
Copy down to the last row of data in col B. Joy? hit the YES below
--
Max
Singapore
---
"Erik" wrote:
I have two columns of years. Column A has 27 years from 1911 to 2007
indicating a particular year a house was built. Column B has all years from
1800 to 2009 use to plot a graph. In column C I want to find if any of the
years from 1800 through 2009 matchs one of the 27 years in column A and
return a 1 if that year matches or a 0 if it does not. With the results in
column C I can graph all years on the x axis and which one of them had a
house built

Finding matches in two columns
 

Max, You are a genius and many thanks. Would you also be so kind to explain
what is going on using IF and COUNTIF? Thanks,
Erik
--
Erik (a Word 2007 user)


"Max" wrote:

One way using COUNTIF
In C2: =IF(B2="","",IF(COUNTIF(A:A,B2),1,0))
Copy down to the last row of data in col B. Joy? hit the YES below
--
Max
Singapore
---
"Erik" wrote:
I have two columns of years. Column A has 27 years from 1911 to 2007
indicating a particular year a house was built. Column B has all years from
1800 to 2009 use to plot a graph. In column C I want to find if any of the
years from 1800 through 2009 matchs one of the 27 years in column A and
return a 1 if that year matches or a 0 if it does not. With the results in
column C I can graph all years on the x axis and which one of them had a
house built

Finding matches in two columns
 

This is the base IF formula: IF(COUNTIF(A:A,B2),1,0)

COUNTIF(A:A,B2) returns the number of times that the value in B2 is found
within col A. If B2 is found once, you'd get 1 as the return, 2 if twice and
so on.

Then .. the above used within the IF construct: IF(COUNTIF(A:A,B2),1,0)
the IF will evaluate any number* greater than zero as TRUE, zero as FALSE
*ie the number returned by the COUNTIF
--
Max
Singapore
---
"Erik" wrote:
Max, You are a genius and many thanks. Would you also be so kind to explain
what is going on using IF and COUNTIF? Thanks,
Erik
--
Erik (a Word 2007 user)

Finding matches in two columns
 

the IF will evaluate any number* greater than zero
as TRUE, zero as FALSE
*ie the number returned by the COUNTIF

That's correct in the context of this particular application.

To be more specific...

IF will evaluate *any number other than 0* as TRUE, zero as FALSE. Text will
return an error.

=IF(-1 = TRUE
=IF(0 = FALSE
=IF(0.00000000025 = TRUE
=IF(-0.03897 = TRUE
=IF(1E100 = TRUE
=IF("text" = #VALUE!

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
This is the base IF formula: IF(COUNTIF(A:A,B2),1,0)

COUNTIF(A:A,B2) returns the number of times that the value in B2 is found
within col A. If B2 is found once, you'd get 1 as the return, 2 if twice
and
so on.

Then .. the above used within the IF construct: IF(COUNTIF(A:A,B2),1,0)
the IF will evaluate any number* greater than zero as TRUE, zero as FALSE
*ie the number returned by the COUNTIF
--
Max
Singapore
---
"Erik" wrote:
Max, You are a genius and many thanks. Would you also be so kind to
explain
what is going on using IF and COUNTIF? Thanks,
Erik
--
Erik (a Word 2007 user)