Return a value for every instance in column based on lookup in another column

[jmewebb]

Return "yes" or "no" for every instance in Column A if specific text exists in Column B.

Example:

Fruit	Color	Do any of the specific fruits contain Red?
Apple	Brown	Yes
Banana	Yellow	No
Pear	Green	No
Apple	Green	Yes
Orange	Red	Yes
Apple	Red	Yes

 

[DRSteele]

I think this might do it, ugly as it is.

MrExcel posts20.xlsx
	C	D	E	F
2				Red
3
4	Fruit	Color	Do any of the specific fruits contain Red?
5	Apple	Brown	Yes
6	Banana	Yellow	No
7	Pear	Green	No
8	Apple	Green	Yes
9	Orange	Red	Yes
10	Apple	Red	Yes
Sheet10

Cell Formulas
Range	Formula
E5:E10	E5	=IF(SUM(--(C5=IF($D$5:$D$10=$F$2,$C$5:$C$10))),"Yes","No")