Conditional Statement Question with VLOOKUP

[RBusiness]

Hello,

I'm hoping you can help?

I have two Sheets. One named "sheet" and the other called "data".
In "sheet" i have a list of text, numbers and characters.

From "sheet" I want to look at "data" and see if the characters in a cell in "data" A1 (just an example) is found in column C of "data" and say "YES" only if the word in the adjacent cell matches the word "green".


SHEET (BEFORE)

widget 1000
no widgets here
widgets 3000
widget 9000

<tbody>
</tbody>

DATA (The other sheet)
Has the data I'm referencing.

A	B	C	D
	yellow	widgets 3000
	red	no widgets here
	green	widget 9000
	green	widget 1000

<tbody>
</tbody>

SHEET (AFTER)
This is the result that I'm looking for. It should say "yes" when "widget 1000" or any of the other phrase exist in column C and have the word "green" in the adjacent cell.

widget 1000	YES
no widgets here	NO
widgets 3000	NO
widget 9000	YES

<tbody>
</tbody>

What do you think?

 

[Weazel]

Depending on the version of excel you have maybe ​....

Excel 2012

	A	B	C
1	Widget	Yes or No	Yes or No
2	widget 1000	Yes	Yes
3	no widgets here	No	No
4	widgets 3000	No	No
5	widget 9000	Yes	Yes

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet​

Worksheet Formulas Cell Formula B2 =IF(SUMPRODUCT((Data!$B$2:$B$5="green")*(Data!$C$2:$C$5=A2)),"Yes","No") C2 =IF(COUNTIFS(Data!$B$2:$B$5,"green",Data!$C$2:$C$5,Sheet!A2),"Yes","No") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[vogel997]

One similar and another one....

=IF(SUMPRODUCT(--(A1=Data!$C$2:$C$5),--("green"=Data!$B$2:$B$5))=1,"Yes","No")

<tbody>
</tbody><colgroup><col></colgroup>

=IF(OFFSET(Data!$B$1,MATCH(A1,Data!$C$2:$C$5,0),)="green","Yes","No")

<tbody>
</tbody><colgroup><col></colgroup>