If Criteria Match , Return yes , no

[Velven]

What I will fill in D is , If B1 = No , Match A1 with C:C If Match is true , Return Yes , otherwise No . ( Drag down to copy )

Is there any better formula or how do I put it together ?

	A	B	C	D
1	John@John.com	No	Cast@cast.com
2	michael@michael.com	No	Rum@rum.com
3	R@R.com	Yes	W@w.com
4	W@W.com	No	F@F.com

<tbody>
</tbody>

 

[FormR]

Hi, here is one option you can try:

Excel 2013/2016
	A	B	C	D
1	John@John.com	No	Cast@cast.com	No
2	michael@michael.com	No	Rum@rum.com	No
3	R@R.com	Yes	W@w.com	No
4	W@W.com	No	F@F.com	Yes
Sheet1

Cell Formulas
Range	Formula
D1	=IF(AND(B1="No",ISNUMBER(MATCH(A1,C:C,0))),"Yes","No")

 

[jtakw]

Hi,

Another way:

Book1
	A	B	C	D
1	John@John.com	No	Cast@cast.com	No
2	michael@michael.com	No	Rum@rum.com	No
3	R@R.com	Yes	W@w.com	No
4	W@W.com	No	F@F.com	Yes
Sheet1

Cell Formulas
Range	Formula
D1	=IF(AND(B1="No",COUNTIF(C$1:C$1000,A1)>0),"Yes","No")

D1 formula copied down.