need data using right function

[sksanjeev786]

Hi Team

I need RCB data after "-" and i have applied the funtion but getting error

RCB_1 "Faf du Plessis - (RCB)"

IPL predict & win LEADERBOARD_2023.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O
1	MN	Date	Match	Venues	Time In IST	Results	MOM				5	5
2	1	31-Mar	CSK vs GT	Ahmedabad	7:30 PM	GT won by 5 wkts	Rashid Khan	154	GT_20 "Rashid Khan - (GT)"		#VALUE!	RCB_1
3	2	01-Apr	PBKS vs KKR	Mohali	3:30PM	PBKS won by 7 runs (DLS)	Arshdeep Singh	224	PBKS_17 "Arshdeep Singh - (PBKS)"
4	3	01-Apr	LSG vs DC	Lucknow	7:30 PM	LSG won by 50 runs	Mark Wood	12	LSG_12 "Mark Wood - (LSG)"
5	4	02-Apr	SRH vs RR	Hyderabad	3:30 PM	RR won by 72 runs	Jos Buttler	57	RR_3 "Jos Buttler - (RR)"
6	5	02-Apr	RCB vs MI	Bengaluru	7:30 PM	RCB won by 8 wkts	Faf du Plessis	28	RCB_1 "Faf du Plessis - (RCB)"
Player Names

Cell Formulas
Range	Formula
K2:K6	K2	=MATCH("*"&J2&"*",$A$1:$A$256,0)
L2:L6	L2	=TRIM(INDEX($A$1:$A$256,MATCH("*"&J2&"*",$A$1:$A$256,0)))
N2	N2	=RIGHT(INDEX($D$2:$L$75,MATCH(N$1,$D$2:$D$75,0),7),LEN(INDEX($D$2:$L$75,MATCH(N$1,$D$2:$D$75,0),7)-FIND("-",INDEX($D$2:$L$75,MATCH(N$1,$D$2:$D$75,0),7))))
O2	O2	=LEFT(INDEX($D$2:$L$75,MATCH(O$1,$D$2:$D$75,0),9),FIND("""",(INDEX($D$2:$L$75,MATCH(O$1,$D$2:$D$75,0),9)))-1)

Named Ranges
Name	Refers To	Cells
'Player Names'!_FilterDatabase	='Player Names'!$A$1:$A$743	K2:L6

 

[awoohaw]

here it is with the clean up. But you are only getting ROW 5 for all of these based on your formula using cell N1.

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(INDEX($D$2:$L$6,MATCH(N$1,$D$2:$D$6,0),9),LEN(INDEX($D$2:$L$6,MATCH(N$1,$D$2:$D$6,0),9))-FIND("-",INDEX($D$2:$L$6,MATCH(N$1,$D$2:$D$6,0),9))),CHAR(34),""),"(",""),")",""))

 

[awoohaw]

with 365 you can also use the textbefore and textafter the find/Len/Etc/Right functions.
it is a lot cleaner:

=TEXTBEFORE(TEXTAFTER(INDEX($D$2:$L$6,MATCH(N$1,$D$2:$D$6,0),9),"("),")")

 

[sksanjeev786]

with 365 you can also use the textbefore and textafter the find/Len/Etc/Right functions.
it is a lot cleaner:

=TEXTBEFORE(TEXTAFTER(INDEX($D$2:$L$6,MATCH(N$1,$D$2:$D$6,0),9),"("),")")

Wow!!!!!!

Simply Awesome!!!

Thank you so much for your help on this

 

[awoohaw]

You're welcome. Happy to help.

If you think a post here has provided you with a solution to your question, please mark that post as the answer.

Best Wishes.

 

[sksanjeev786]

You're welcome. Happy to help.

If you think a post here has provided you with a solution to your question, please mark that post as the answer.

Best Wishes.

Sure!!!