Finding a value in two lists. Should be simple but...

[auballard]

I'm using Microsoft Office for Mac 2011 and I'm simply trying to find if a value from one list is in another list. In this case I want to see if MM33 (A2) from list A is in List B. I've tried using a column array, a range, and cell specific formulas. The column array and range does not find the match.

[TABLE="width: 886"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3, align: center"]Results[/TD]
[/TR]
[TR]
[TD="align: center"]ListA[/TD]
[TD="align: center"]ListB[/TD]
[TD="align: center"]Using Column Array '=IF(A2=B:B,"Yes","No")[/TD]
[TD="align: center"]Using Range,=IF(A2=B2:B6,"Yes","No")[/TD]
[TD="align: center"]Using Cell =A2=B3[/TD]
[/TR]
[TR]
[TD="align: center"]MM333[/TD]
[TD="align: center"]MM543[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]MM345[/TD]
[TD="align: center"]MM333[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]MM456[/TD]
[TD="align: center"]MM876[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]MM765[/TD]
[TD="align: center"]MM35[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]MM453[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If I use the column array or a range the MM333 value is not found in the List B. If I specify the cells it finds the match which is useless for my purposes. I am using the array formula (Control/Shift/Enter) in the formula but nothing seems to work.

Thank you!
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style>

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style><style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style>

 

[Scott T]

Book1
	A	B	C	D
1	ListA	ListB
2	MM333	MM543		Yes
3	MM345	MM333
4	MM456	MM876
5	MM765	MM35
6		MM453
Sheet1

Cell Formulas
Range	Formula
D2	=IF(ISNUMBER(MATCH(A2,$B$2:$B$6,0)),"Yes","No")