Find 2nd Value in a Row, Return Adjacent Cell

[MeditateMan]

Hello, been reading up on the forms and trying different solutions but I am stumped.

I have a row with an NFL Position, and to the right of it the NFL Player's Name. However there are multiple instances of RB and WR.

I need assistance in filling in columns V,X,Y with the 2nd Instance of RB (V), 2nd Instance of WR (X), 3rd Instance of WR (Y) from the same row.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1																				QB	RB	RB	WR	WR	WR	TE	Flex	DST
2	QB	Matt Ryan	RB	Najee Harris	RB	Saquon Barkley	FLEX	Justin Jefferson	WR	Brandin Cooks	WR	Michael Pittman	WR	Jahan Dotson	TE	Travis Kelce	DST	Steelers		Matt Ryan	Najee Harris		Brandin Cooks			Travis Kelce	Justin Jefferson	Steelers
3	QB	Jameis Winston	RB	D'Andre Swift	FLEX	Saquon Barkley	RB	Dameon Pierce	WR	Justin Jefferson	WR	Michael Pittman	WR	Christian Kirk	TE	Travis Kelce	DST	Lions		Jameis Winston	D'Andre Swift		Justin Jefferson			Travis Kelce	Saquon Barkley	Lions
4	QB	Trevor Lawrence	RB	Christian McCaffrey	RB	Saquon Barkley	FLEX	Justin Jefferson	WR	Christian Kirk	WR	Marvin Jones	WR	Jahan Dotson	TE	Travis Kelce	DST	Steelers		Trevor Lawrence	Christian McCaffrey		Christian Kirk			Travis Kelce	Justin Jefferson	Steelers
5	QB	Jalen Hurts	RB	Christian McCaffrey	RB	Saquon Barkley	WR	A.J. Brown	WR	Michael Pittman	WR	Jahan Dotson	FLEX	Travis Kelce	TE	Dallas Goedert	DST	Vikings		Jalen Hurts	Christian McCaffrey		A.J. Brown			Dallas Goedert	Travis Kelce	Vikings
6	QB	Carson Wentz	RB	Christian McCaffrey	RB	Saquon Barkley	WR	Terry McLaurin	WR	Michael Pittman	WR	Christian Kirk	FLEX	Jahan Dotson	TE	Travis Kelce	DST	Falcons		Carson Wentz	Christian McCaffrey		Terry McLaurin			Travis Kelce	Jahan Dotson	Falcons
7	QB	Patrick Mahomes	RB	Nick Chubb	RB	Saquon Barkley	WR	DJ Moore	WR	JuJu Smith-Schuster	WR	Christian Kirk	TE	Travis Kelce	FLEX	Zach Ertz	DST	Steelers		Patrick Mahomes	Nick Chubb		DJ Moore			Travis Kelce	Zach Ertz	Steelers
8	QB	Marcus Mariota	RB	Jonathan Taylor	RB	Saquon Barkley	WR	Michael Pittman	WR	Drake London	WR	Jakobi Meyers	FLEX	Travis Kelce	TE	Kyle Pitts	DST	Steelers		Marcus Mariota	Jonathan Taylor		Michael Pittman			Kyle Pitts	Travis Kelce	Steelers
9	QB	Patrick Mahomes	RB	Jonathan Taylor	RB	Saquon Barkley	FLEX	Marquise Brown	WR	JuJu Smith-Schuster	WR	Jahan Dotson	WR	Wan'Dale Robinson	TE	Travis Kelce	DST	Dolphins		Patrick Mahomes	Jonathan Taylor		JuJu Smith-Schuster			Travis Kelce	Marquise Brown	Dolphins
10	QB	Kirk Cousins	RB	Aaron Jones	RB	Saquon Barkley	WR	Davante Adams	WR	Justin Jefferson	FLEX	Adam Thielen	WR	Jahan Dotson	TE	Gerald Everett	DST	Dolphins		Kirk Cousins	Aaron Jones		Davante Adams			Gerald Everett	Adam Thielen	Dolphins
11	QB	Jalen Hurts	RB	Joe Mixon	RB	Aaron Jones	FLEX	Saquon Barkley	WR	Justin Jefferson	WR	Parris Campbell	WR	Romeo Doubs	TE	Travis Kelce	DST	Dolphins		Jalen Hurts	Joe Mixon		Justin Jefferson			Travis Kelce	Saquon Barkley	Dolphins
12	QB	Patrick Mahomes	RB	Joe Mixon	RB	Saquon Barkley	FLEX	Marquise Brown	WR	Michael Pittman	WR	JuJu Smith-Schuster	WR	Randall Cobb	TE	Travis Kelce	DST	Vikings		Patrick Mahomes	Joe Mixon		Michael Pittman			Travis Kelce	Marquise Brown	Vikings
13	QB	Kirk Cousins	RB	Aaron Jones	RB	Saquon Barkley	FLEX	Justin Jefferson	WR	Michael Pittman	WR	Adam Thielen	WR	Jahan Dotson	TE	Travis Kelce	DST	Giants		Kirk Cousins	Aaron Jones		Michael Pittman			Travis Kelce	Justin Jefferson	Giants
Sheet1

Cell Formulas
Range	Formula
T2:T13	T2	=B2
U2:U13	U2	=INDEX(A2:Q2,MATCH("RB",A2:R2,0)+1)
Z2:Z13	Z2	=INDEX(A2:Q2,MATCH("TE",A2:R2,0)+1)
AA2:AA13	AA2	=INDEX(A2:Q2,MATCH("FLEX",A2:R2,0)+1)
AB2:AB13	AB2	=R2
W2:W13	W2	=INDEX(A2:Q2,MATCH("wr",A2:R2,0)+1)

 

[JamesCanale]

Ok, I have acquired textsplit - is there a better/different way than what you showed?

I don't have textsplit yet. I look forward to seeing it in action.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	QB Matt Ryan RB Najee Harris RB Saquon Barkley FLEX Justin Jefferson WR Brandin Cooks WR Michael Pittman Jr. WR Jahan Dotson TE Travis Kelce DST Steelers	QB	Matt Ryan	RB	Najee Harris	RB	Saquon Barkley	FLEX	Justin Jefferson	WR	Brandin Cooks	WR	Michael Pittman Jr.	WR	Jahan Dotson	TE	Travis Kelce	DST	Steelers
3	QB Jameis Winston RB D'Andre Swift FLEX Saquon Barkley RB Dameon Pierce WR Justin Jefferson WR Michael Pittman Jr. WR Christian Kirk TE Travis Kelce DST Lions	QB	Jameis Winston	RB	D'Andre Swift	FLEX	Saquon Barkley	RB	Dameon Pierce	WR	Justin Jefferson	WR	Michael Pittman Jr.	WR	Christian Kirk	TE	Travis Kelce	DST	Lions
4	QB Trevor Lawrence RB Christian McCaffrey RB Saquon Barkley FLEX Justin Jefferson WR Christian Kirk WR Marvin Jones Jr. WR Jahan Dotson TE Travis Kelce DST Steelers	QB	Trevor Lawrence	RB	Christian McCaffrey	RB	Saquon Barkley	FLEX	Justin Jefferson	WR	Christian Kirk	WR	Marvin Jones Jr.	WR	Jahan Dotson	TE	Travis Kelce	DST	Steelers
5	QB Jalen Hurts RB Christian McCaffrey RB Saquon Barkley WR A.J. Brown WR Michael Pittman Jr. WR Jahan Dotson FLEX Travis Kelce TE Dallas Goedert DST Vikings	QB	Jalen Hurts	RB	Christian McCaffrey	RB	Saquon Barkley	WR	A.J. Brown	WR	Michael Pittman Jr.	WR	Jahan Dotson	FLEX	Travis Kelce	TE	Dallas Goedert	DST	Vikings
6	QB Carson Wentz RB Christian McCaffrey RB Saquon Barkley WR Terry McLaurin WR Michael Pittman Jr. WR Christian Kirk FLEX Jahan Dotson TE Travis Kelce DST Falcons	QB	Carson Wentz	RB	Christian McCaffrey	RB	Saquon Barkley	WR	Terry McLaurin	WR	Michael Pittman Jr.	WR	Christian Kirk	FLEX	Jahan Dotson	TE	Travis Kelce	DST	Falcons
Input

Cell Formulas
Range	Formula
B2:S6	B2	=LET(txt,DROP(TRIM(TEXTSPLIT(A2,{"QB","RB","WR","TE","FLEX","DST"})),,1),DROP(REDUCE("",txt,LAMBDA(a,b,HSTACK(a,TEXTAFTER(TEXTBEFORE(" "&A2,b,,,,"")," ",-2),b))),,1))
Dynamic array formulas.

 

[Fluff]

Slight tweak to get rid of the trailing spaces.

=LET(txt,DROP(TEXTSPLIT(A2,{"QB","RB","WR","TE","FLEX","DST"}),,1),DROP(REDUCE("",txt,LAMBDA(a,b,TRIM(HSTACK(a,TEXTAFTER(TEXTBEFORE(" "&A2,b,,,,"")," ",-1),b)))),,1))