Compare two columns
 

I am wanting to compare column A with B - and if they match then put a yes in
column c. The formula I am using isn't working - Please help!
=IF(ISNA(MATCH(A2,B$2:B$22)),"yes","")
For example:
A B
1.00 1.00
2.00 6.00
3.00 2.00
4.00 4.00
5.00 3.00
6.00 1.00
8.00 10.00
10.00 9.00
9.00 0.18
10.00 0.13
0.11 0.18 yes
0.12 0.18 yes
0.13 0.18 yes
0.14 0.18 yes
0.15 0.18 yes
0.16 0.18 yes
0.17 1.00 yes
0.18 0.18
0.19 0.18
0.20 0.20
0.18 0.18

Thanks.....

Compare two columns
 

=IF(A2=B2,"YES")

I hope this helps

"pm" wrote:

I am wanting to compare column A with B - and if they match then put a yes in
column c. The formula I am using isn't working - Please help!
=IF(ISNA(MATCH(A2,B$2:B$22)),"yes","")
For example:
A B
1.00 1.00
2.00 6.00
3.00 2.00
4.00 4.00
5.00 3.00
6.00 1.00
8.00 10.00
10.00 9.00
9.00 0.18
10.00 0.13
0.11 0.18 yes
0.12 0.18 yes
0.13 0.18 yes
0.14 0.18 yes
0.15 0.18 yes
0.16 0.18 yes
0.17 1.00 yes
0.18 0.18
0.19 0.18
0.20 0.20
0.18 0.18

Thanks.....

Compare two columns
 

The match function returns the position of a value in a table. It does not
return YES/NO.

Try this: =IF(A3=B3,"YES","NO") using the proper cell references.

Also be careful about precision. if cell A1 = .11 and cell B1 = .111 but
Excel is set to show only two decimal places, then cell B2 will display .11
but contain .111 and the value will not equal that in A1.



"pm" wrote:

I am wanting to compare column A with B - and if they match then put a yes in
column c. The formula I am using isn't working - Please help!
=IF(ISNA(MATCH(A2,B$2:B$22)),"yes","")
For example:
A B
1.00 1.00
2.00 6.00
3.00 2.00
4.00 4.00
5.00 3.00
6.00 1.00
8.00 10.00
10.00 9.00
9.00 0.18
10.00 0.13
0.11 0.18 yes
0.12 0.18 yes
0.13 0.18 yes
0.14 0.18 yes
0.15 0.18 yes
0.16 0.18 yes
0.17 1.00 yes
0.18 0.18
0.19 0.18
0.20 0.20
0.18 0.18

Thanks.....

Compare two columns
 

Thanks. Let me see if I can better explain what I am wanting to accomplish.
I want to look at column A1 and compare to column B1:B22. Then A2 and
compare B1:B22, and so on...until I get to A22 comparing B1:B22, and if there
is a match place a yes next to column A#...basically I am wanting to match
one to many....thanks.

"Flick Olmsford" wrote:

The match function returns the position of a value in a table. It does not
return YES/NO.

Try this: =IF(A3=B3,"YES","NO") using the proper cell references.

Also be careful about precision. if cell A1 = .11 and cell B1 = .111 but
Excel is set to show only two decimal places, then cell B2 will display .11
but contain .111 and the value will not equal that in A1.



"pm" wrote:

I am wanting to compare column A with B - and if they match then put a yes in
column c. The formula I am using isn't working - Please help!
=IF(ISNA(MATCH(A2,B$2:B$22)),"yes","")
For example:
A B
1.00 1.00
2.00 6.00
3.00 2.00
4.00 4.00
5.00 3.00
6.00 1.00
8.00 10.00
10.00 9.00
9.00 0.18
10.00 0.13
0.11 0.18 yes
0.12 0.18 yes
0.13 0.18 yes
0.14 0.18 yes
0.15 0.18 yes
0.16 0.18 yes
0.17 1.00 yes
0.18 0.18
0.19 0.18
0.20 0.20
0.18 0.18

Thanks.....

Compare two columns
 

=if(countif($b$1:$b$22,a1)0,"at least one match","nope")

or

=if(isnumber(match(a1,$b$1:$b$22,0)),"Yep","nope")



pm wrote:

Thanks. Let me see if I can better explain what I am wanting to accomplish.
I want to look at column A1 and compare to column B1:B22. Then A2 and
compare B1:B22, and so on...until I get to A22 comparing B1:B22, and if there
is a match place a yes next to column A#...basically I am wanting to match
one to many....thanks.

"Flick Olmsford" wrote:

The match function returns the position of a value in a table. It does not
return YES/NO.

Try this: =IF(A3=B3,"YES","NO") using the proper cell references.

Also be careful about precision. if cell A1 = .11 and cell B1 = .111 but
Excel is set to show only two decimal places, then cell B2 will display .11
but contain .111 and the value will not equal that in A1.



"pm" wrote:

I am wanting to compare column A with B - and if they match then put a yes in
column c. The formula I am using isn't working - Please help!
=IF(ISNA(MATCH(A2,B$2:B$22)),"yes","")
For example:
A B
1.00 1.00
2.00 6.00
3.00 2.00
4.00 4.00
5.00 3.00
6.00 1.00
8.00 10.00
10.00 9.00
9.00 0.18
10.00 0.13
0.11 0.18 yes
0.12 0.18 yes
0.13 0.18 yes
0.14 0.18 yes
0.15 0.18 yes
0.16 0.18 yes
0.17 1.00 yes
0.18 0.18
0.19 0.18
0.20 0.20
0.18 0.18

Thanks.....


--

Dave Peterson

Compare two columns
 

OPTION 1:
=IF(COUNTIF($B$2:$B$22,A2) 1, "YES", "NO")

This will show you "YES" or "NO" (if you don't want "NO", just type in "")

OPTION 2:
If you use this formula and you want to know how many times it show up, you
can also do:
=IF(COUNTIF($B$2:$B$22,A2) 1, "YES - " & COUNTIF($B$2:$B$22,A2) & "
times", "NO")

This will show you "YES - x times" or "NO"
--
E. Aimee Bauer, PMP


"pm" wrote:

Thanks. Let me see if I can better explain what I am wanting to accomplish.
I want to look at column A1 and compare to column B1:B22. Then A2 and
compare B1:B22, and so on...until I get to A22 comparing B1:B22, and if there
is a match place a yes next to column A#...basically I am wanting to match
one to many....thanks.

"Flick Olmsford" wrote:

The match function returns the position of a value in a table. It does not
return YES/NO.

Try this: =IF(A3=B3,"YES","NO") using the proper cell references.

Also be careful about precision. if cell A1 = .11 and cell B1 = .111 but
Excel is set to show only two decimal places, then cell B2 will display .11
but contain .111 and the value will not equal that in A1.



"pm" wrote:

I am wanting to compare column A with B - and if they match then put a yes in
column c. The formula I am using isn't working - Please help!
=IF(ISNA(MATCH(A2,B$2:B$22)),"yes","")
For example:
A B
1.00 1.00
2.00 6.00
3.00 2.00
4.00 4.00
5.00 3.00
6.00 1.00
8.00 10.00
10.00 9.00
9.00 0.18
10.00 0.13
0.11 0.18 yes
0.12 0.18 yes
0.13 0.18 yes
0.14 0.18 yes
0.15 0.18 yes
0.16 0.18 yes
0.17 1.00 yes
0.18 0.18
0.19 0.18
0.20 0.20
0.18 0.18

Thanks.....

Compare two columns
 

Thanks Aimee,

This works great!

"Aimee" wrote:

OPTION 1:
=IF(COUNTIF($B$2:$B$22,A2) 1, "YES", "NO")

This will show you "YES" or "NO" (if you don't want "NO", just type in "")

OPTION 2:
If you use this formula and you want to know how many times it show up, you
can also do:
=IF(COUNTIF($B$2:$B$22,A2) 1, "YES - " & COUNTIF($B$2:$B$22,A2) & "
times", "NO")

This will show you "YES - x times" or "NO"
--
E. Aimee Bauer, PMP


"pm" wrote:

Thanks. Let me see if I can better explain what I am wanting to accomplish.
I want to look at column A1 and compare to column B1:B22. Then A2 and
compare B1:B22, and so on...until I get to A22 comparing B1:B22, and if there
is a match place a yes next to column A#...basically I am wanting to match
one to many....thanks.

"Flick Olmsford" wrote:

The match function returns the position of a value in a table. It does not
return YES/NO.

Try this: =IF(A3=B3,"YES","NO") using the proper cell references.

Also be careful about precision. if cell A1 = .11 and cell B1 = .111 but
Excel is set to show only two decimal places, then cell B2 will display .11
but contain .111 and the value will not equal that in A1.



"pm" wrote:

I am wanting to compare column A with B - and if they match then put a yes in
column c. The formula I am using isn't working - Please help!
=IF(ISNA(MATCH(A2,B$2:B$22)),"yes","")
For example:
A B
1.00 1.00
2.00 6.00
3.00 2.00
4.00 4.00
5.00 3.00
6.00 1.00
8.00 10.00
10.00 9.00
9.00 0.18
10.00 0.13
0.11 0.18 yes
0.12 0.18 yes
0.13 0.18 yes
0.14 0.18 yes
0.15 0.18 yes
0.16 0.18 yes
0.17 1.00 yes
0.18 0.18
0.19 0.18
0.20 0.20
0.18 0.18

Thanks.....

Compare two columns
 

Dave - this works great! thank you very much!

"Dave Peterson" wrote:

=if(countif($b$1:$b$22,a1)0,"at least one match","nope")

or

=if(isnumber(match(a1,$b$1:$b$22,0)),"Yep","nope")



pm wrote:

Thanks. Let me see if I can better explain what I am wanting to accomplish.
I want to look at column A1 and compare to column B1:B22. Then A2 and
compare B1:B22, and so on...until I get to A22 comparing B1:B22, and if there
is a match place a yes next to column A#...basically I am wanting to match
one to many....thanks.

"Flick Olmsford" wrote:

The match function returns the position of a value in a table. It does not
return YES/NO.

Try this: =IF(A3=B3,"YES","NO") using the proper cell references.

Also be careful about precision. if cell A1 = .11 and cell B1 = .111 but
Excel is set to show only two decimal places, then cell B2 will display .11
but contain .111 and the value will not equal that in A1.



"pm" wrote:

I am wanting to compare column A with B - and if they match then put a yes in
column c. The formula I am using isn't working - Please help!
=IF(ISNA(MATCH(A2,B$2:B$22)),"yes","")
For example:
A B
1.00 1.00
2.00 6.00
3.00 2.00
4.00 4.00
5.00 3.00
6.00 1.00
8.00 10.00
10.00 9.00
9.00 0.18
10.00 0.13
0.11 0.18 yes
0.12 0.18 yes
0.13 0.18 yes
0.14 0.18 yes
0.15 0.18 yes
0.16 0.18 yes
0.17 1.00 yes
0.18 0.18
0.19 0.18
0.20 0.20
0.18 0.18

Thanks.....


--

Dave Peterson