Checking a column of data against another set and assign 1 next to it

[dilla1988]

Hey guys,

The best excel help forum so far. For a graduate employee like me this has been huge help.

anyway i have a new situation.

i have a column 1 with values as follows:

12594455
12594480
12594517
12594534

and then another, column 2 :
12594455
12594455
12594455
12594455
12594455
12594455
12594455
12594455
12594455
12594480
12594480
12594517
12594517
12594517
12594517
12594517
12594534
12594534
12594534
etc;

now i want to find if column 2 matches with column 1, i want to put "1" next to column 2 entries. Mind that there will be different values within column 2 so they would have "0" next to it. whole end point of this is for me to filter out the "1" and copy the headers.

thanks

 

[dilla1988]

would this work guys?

=IF(ISERROR(MATCH(F1,$A$1:$A$18,0)),"0","1")

 

[bschwartz]

Is this what you're looking for?

Excel 2010
	A	B	C
1	Column 1	Column 2	Match?
2	12594455	12594455	1
3	12594480	12594455	1
4	12594517	12594455	1
5	12594534	12594455	1
6		12594455	1
7		12594455	1
8		12594455	1
9		12594455	1
10		12594455	1
11		12594478	0
12		12594480	1
13		12594480	1
14		12594517	1
15		12594517	1
16		12594517	1
17		12594517	1
18		12594517	1
19		12594534	1
20		12594534	1
21		12594534	1
Sheet5

Cell Formulas
Range	Formula
C2	=IFERROR(MATCH(B2,$A$2:$A$999,0)^0,0)

 

[dilla1988]

why in the IF statement value_if_true = 0 when it should be other way round?

I used this and it seems to give results. but i'm confused between ISERROR and IF

 

[bschwartz]

why in the IF statement value_if_true = 0 when it should be other way round?

I used this and it seems to give results. but i'm confused between ISERROR and IF

The formula you posted is checking for a match in column 1. If the match function returns an error, meaning there is NO MATCH, the IF function will return a 0 as text ("0"). Otherwise, if the match function returns a number, meaning it found a match, the IF function will return a 1 as text ("1").

 

[Jim885]

This should also work;

=IF(ISERROR(MATCH(F1,$A$1:$A$18,0)),0,1)

 

[dilla1988]

Hey Ben,

That's precisely I want!

how does it differ to =IF(ISERROR(MATCH(F1,$A$1:$A$18,0)),"0","1") ??

 

[dilla1988]

great both seems to give same results i see..

I'm still learning all these different formulas.

 

[bschwartz]

If you mean which one is better to use, I'm not sure. The one I posted seems a little simpler to me, but they are fairly similar.

The only other difference is that your formula is returning 0s or 1s as text and mine is returning 0s and 1s as numbers (which can be summed or some other math operation). This may or may not matter to you, but can be easily fixed by adjusting your formula as Jim885 suggested above:

This should also work;

=IF(ISERROR(MATCH(F1,$A$1:$A$18,0)),0,1)