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]

you are not giving Column A so it it will be very difficult for the forum to figure your problem out. Can you post a more comprehensive mini worksheet, please?

 

[sksanjeev786]

you are not giving Column A so it it will be very difficult for the forum to figure your problem out. Can you post a more comprehensive mini worksheet, please?

Hi,

I need data for Column "N2" Cell and for that cell array Starts from D column onwards I need only all data including bracket i.e."(RCB)" with the Right function as of now i am getting an error
by using the below formula

=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))))

But using LEFT Function in "O2" cell got the correct solution.

I hope this information helps you

Regards
Sanjeev

 

[awoohaw]

Okay, i looked at your formula in cell L2. The FIND Function is searching for a "-" in the 7th column in the INDEX range which is column J. all of the text strings in column J do not have a "-". I changed it to 9, I still got #VALUE error. So, can you explain what you are trying to get.. what word from what column? Please give some examples.

 

[awoohaw]

also, your index range includes column L, but you have formulas that reference column L in the index, that can lead to circular reference errors.
nevermind, that formula is not in column L. my bad.

 

[awoohaw]

please post the expected results for ROWS 2-6 in your example?

 

[sksanjeev786]

also, your index range includes column L, but you have formulas that reference column L in the index, that can lead to circular reference errors.
nevermind, that formula is not in column L. my bad.

Hi,

Now i have added all the data from "A to O" column and i need all data which are in Brakcet i,e(GT))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	LSG_1 "KL Rahul - (LSG)"			MN	Date	Match	Venues	Time In IST	Results	MOM				5	5
2	LSG_2 "Manan Vohra - (LSG)"			1	31 March 2023	CSK vs GT	Ahmedabad	0.8125	GT won by 5 wkts	Rashid Khan	154	GT_20 "Rashid Khan - (GT)"		#VALUE!	RCB_1
3	LSG_3 "Quinton de Kock - (LSG)"			2	01 April 2023	PBKS vs KKR	Mohali	3:30PM	PBKS won by 7 runs (DLS)	Arshdeep Singh	224	PBKS_17 "Arshdeep Singh - (PBKS)"
4	LSG_4 "Ayush Badoni - (LSG)"			3	01 April 2023	LSG vs DC	Lucknow	0.8125	LSG won by 50 runs	Mark Wood	12	LSG_12 "Mark Wood - (LSG)"
5	LSG_5 "Deepak Hooda - (LSG)"			4	02 April 2023	SRH vs RR	Hyderabad	0.645833333	RR won by 72 runs	Jos Buttler	57	RR_3 "Jos Buttler - (RR)"
6	LSG_6 "Krishnappa Gowtham - (LSG)"			5	02 April 2023	RCB vs MI	Bengaluru	0.8125	RCB won by 8 wkts	Faf du Plessis	28	RCB_1 "Faf du Plessis - (RCB)"
7	LSG_7 "Karan Sharma - (LSG)"
8	LSG_8 "Krunal Pandya - (LSG)"
9	LSG_9 "Kyle Mayers - (LSG)"
10	LSG_10 "Marcus Stoinis - (LSG)"
11	LSG_11 "Avesh Khan - (LSG)"
12	LSG_12 "Mark Wood - (LSG)"
13	LSG_13 "Mayank Yadav - (LSG)"
14	LSG_14 "Mohsin Khan - (LSG)"
15	LSG_15 "Ravi Bishnoi - (LSG)"
16	LSG_16 "Jaydev Unadkat - (LSG)"
17	LSG_17 "Yash Thakur - (LSG)"
18	LSG_18 "Romario Shepherd - (LSG)"
19	LSG_19 "Nicholas Pooran - (LSG)"
20	LSG_20 "Amit Mishra - (LSG)"
21	LSG_21 "Daniel Sams - (LSG)"
22	LSG_22 "Swapnil Singh - (LSG)"
23	LSG_23 "Prerak Mankad - (LSG)"
24	LSG_24 "Naveen Ul Haq - (LSG)"
25	LSG_25 "Yudhvir Singh - (LSG)"
Sheet1

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)

 

[awoohaw]

so in your examples for column N is this what you want:
GT
PBKS
LSG
RR
RCB
?

with or without the parentheses(I guess you call these brackets, i call square brackets: just brackets ) ?

 

[awoohaw]

also, 4 of the 5 examples are not in the column A that you provided. Can you confirm there are spaces or other non visible space characters in the list?

 

[awoohaw]

you have a close parenthesis (bracket) in the wrong spot:

=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)))