How to find whether a text string occurs in a set of rows

[tjallen]

I have a table with the first column containing names that repeat, and another column containing words. I want to find out whether a specific word "Stop" occurs for each Name in the table, and populate the cells next to each name with either a Y or N. How do I create a formula in E3-E6 to accomplish this?

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff v2.xlsm
	A	B	C	D	E
1
2	Joe	Speed
3	Joe	Speed		Joe	Y
4	Joe	Stop		Mike	N
5	Mike	Light		Norm	Y
6	Mike	Speed		Sam	N
7	Norm	Speed
8	Norm	Speed
9	Norm	Stop
10	Norm	Stop
11	Sam	Light
12	Sam	Park
13	Sam	Lane
Data

Cell Formulas
Range	Formula
D3:D6	D3	=UNIQUE(A2:A13)
E3:E6	E3	=IF(COUNTIFS(A:A,D3#,B:B,"Stop"),"Y","N")
Dynamic array formulas.

 

[tjallen]

Hi & welcome to MrExcel.
How about

+Fluff v2.xlsm
	A	B	C	D	E
1
2	Joe	Speed
3	Joe	Speed		Joe	Y
4	Joe	Stop		Mike	N
5	Mike	Light		Norm	Y
6	Mike	Speed		Sam	N
7	Norm	Speed
8	Norm	Speed
9	Norm	Stop
10	Norm	Stop
11	Sam	Light
12	Sam	Park
13	Sam	Lane
Data

Cell Formulas
Range	Formula
D3:D6	D3	=UNIQUE(A2:A13)
E3:E6	E3	=IF(COUNTIFS(A:A,D3#,B:B,"Stop"),"Y","N")
Dynamic array formulas.

I'm embarrassed to say that works! I obviously need to better understand array formulas, as I was trying to go down Vlookup and Match routes. Out of curiosity, what does the "D3#" represent? I found I had to delete the "#" sign to make it work.

Thanks again

 

[Fluff]

The # sign indicates that D3 is the start of a spill range & so the countifs formula will then spill down to match.

Also the Countifs formula is not an array formula.