Easier formula approach?

[Stephen_IV]

I received this from a someone. Is there an easier formula that someone can come up with. Besides VBA this is all I could come up with.

Book1
	A	B	C	D
1	Connecticut		Maine	Sonny
2	Steve			Johnny
3	Ray			Angelo
4	Bob			Salvatore
5	Jimmy			Gino
6	New York
7	Jay
8	Fred
9	Mary
10	Ted
11	Sam
12	Maine
13	Sonny
14	Johnny
15	Angelo
16	Salvatore
17	Gino
18	California
19	Gus
20	Dave
21	Ken
22	Ralph
23	George
24	Manny
Sheet1 (2)

Cell Formulas
Range	Formula
D1:D5	D1	=LET( s,C1, r,A1:A24, states,{"Connecticut","New York","Maine","California"}, start,MATCH(s,r,0)+1, nextState,IFERROR(MATCH(TRUE,ISNUMBER(MATCH(OFFSET(r,start,0,ROWS(r)-start),states,0)),0)+start,ROWS(r)+1), FILTER(r,(ROW(r)>=start)*(ROW(r)<nextState)) )
Dynamic array formulas.

 

[Fluff]

Not sure if you consider it "easier", but how about

=LET(d,A1:A100,states,{"Connecticut","New York","Maine","California"},r,HSTACK(XMATCH(states,d),COUNTA(d)+1),s,XMATCH(C1,d),DROP(TAKE(d,INDEX(r,XMATCH(s+1,r,1))-1),s))

 

[Stephen_IV]

Thanks Fluff. I like the way you used drop take index match. Always learning something! Thank you again!

 

[Cubist]

Not really shorter but an alternative.

=LET(d,A1:A24,DROP(FILTER(d,SCAN("",IF(ISNUMBER(SEARCH(d,TEXTJOIN(",",,{"Connecticut";"New York";"Maine";"California"}))),d,""),LAMBDA(a,b,IF(b="",a,b)))=C1),1))

 

[Stephen_IV]

Cubist! Nice!!! Great approach!

 

[Cubist]

Thanks. The SCAN trick I learned from Fluff.