Compare two columns

[pm]

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

 

[Zee]

=IF(A2=B2,"YES")

I hope this helps

 

[Flick Olmsford]

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]

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.

 

[Dave Peterson]

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

 

[Aimee]

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"

 

[pm]

Thanks Aimee,

This works great!

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"

 

[pm]

Dave - this works great! thank you very much!

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