Find a key word then use that to check down a column

[TailsFails1979]

Howdy,

Hopefully an easy one.

See the attached data sheet.

What i am trying to do is in Column F Starting at F3 i want a formula that can do the following

1. Find the word Pineapples
2. Look down the Pineapples Column and if there is a 1 return 'Yes' or a 0 return 'No'

That is the easy bit, however Column A-D is a pivot table and there is a possibility that Pineapples moves from Column D to Column C, so it needs to be flexible enough to first of all find Pineapples, then go down in that column and complete the Yes or No part of the formula.

Lastly - if the formula is flexible enough - i will also put in Column G the same formula but search for Peaches, and so on

Any help is appreciated

 

[TailsFails1979]

	PIVOT Table
1	A	B	C	D	E	F
2	Apples	Bananas	Peaches	Pineapples		Formula
3	1	0	0	1		Yes
4	0	1	0	0		No
5	1	1	1	1		Yes
6	0	1	0	1		Yes
7	1	1	0	0		No
8	0	0	0	1		Yes
9	1	1	1	0		No
10	0	0	1	1		Yes
11	1	0	1	1		Yes
12	0	0	0	0		No
13	0	0	1	1		Yes
14	0	0	1	0		No
15	0	1	0	1		Yes
16	0	1	0	1		Yes

<tbody>
</tbody>

 

[TailsFails1979]

Anyone wanna help?

 

[Peter_SSs]

Welcome to the MrExcel board!

Like this - copied across and down?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Apples	Bananas	Peaches	Pineapples		Pineapples	Bananas	Peaches	Apples
2	1	0	0	1		Yes	No	No	Yes
3	0	1	0	0		No	Yes	No	No
4	1	1	1	1		Yes	Yes	Yes	Yes
5	0	1	0	1		Yes	Yes	No	No
6	1	1	0	0		No	Yes	No	Yes
7	0	0	0	1		Yes	No	No	No
8	1	1	1	0		No	Yes	Yes	Yes
9	0	0	1	1		Yes	No	Yes	No
10	1	0	1	1		Yes	No	Yes	Yes
11	0	0	0	0		No	No	No	No
12	0	0	1	1		Yes	No	Yes	No
13	0	0	1	0		No	No	Yes	No
14	0	1	0	1		Yes	Yes	No	No
15	0	1	0	1		Yes	Yes	No	No
Yes No

 

[TailsFails1979]

Thank you - that will work

 

[Peter_SSs]

Thank you - that will work

You're welcome.