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.

		Results
ListA	ListB	Using Column Array '=IF(A2=B:B,"Yes","No")	Using Range,=IF(A2=B2:B6,"Yes","No")	Using Cell =A2=B3
MM333	MM543	No	No	TRUE
MM345	MM333
MM456	MM876
MM765	MM35
	MM453

<tbody>
</tbody>

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