If two word phrase exists in cell, then "1"

[noslenwerd]

Hello,

In the example below, in cell c2, I am trying to search for "Red Apple" (C1), in the cell A3. If it is found, I want C3 to populate with a 1. If it is not, show a 0. As you can see below, it isn't working currently.

Any ideas what is wrong with my formula?

 

[Fluff]

You need to swap the ranges round in the Search function. The 1st reference should be what you want to find.

 

[Joe4]

You have your ranges backwards. Try:

=IF(ISNUMBER(SEARCH(C$1,$A3)),1,0)

Note if you use the "$" like I did in my formula, you will be able to copy that formula to ALL other cells in your grid, and it will adjust correctly.

 

[Eric W]

And since you have Excel 365, you can write your formula in such a way that you don't need to copy the formula to any other cells. Just put this in B2, and the results will SPILL to the rest of the range:

Book1
	A	B	C	D
1	Data	Yellow Banana	Red Apple	Green Apple
2	Yellow Banana, Red Apple	1	1	0
3	Red Apple, Green Apple	0	1	1
4	Yellow Apple, Yellow Watermelon	0	0	0
Sheet11

Cell Formulas
Range	Formula
B2:D4	B2	=ISNUMBER(SEARCH(B1:D1,A2:A4))+0
Dynamic array formulas.

 

[noslenwerd]

Perfect! Thank you both. I knew I shouldn't have had decaf today

 

[Fluff]

Glad we could help & thanks for the feedback.