How do I nest a MATCH inside a SEARCH?

[jamieraitt]

Hello,

First post in here and new to Excel in general, so go easy! Also, if there is a simpler method of doing this then please tell me!

I have been given a spreadsheet which contains the finishing positions of several races in a season. Unfortunately, it's not that easy to do any analysis on in the current format, which is:

BAH MON HUN etc...
1st GLY SWF SWF
2nd NAG JEF BLI
3rd WHI BLI LAW
etc...

I'm trying to get it into a format like this:

BAH MON HUN
BLI 11 3 2
GLY 1 12 DNF
JEF 8 2 11

On the sheet I have been given, if a player has a DNF (Did Not Finish), they have an "x" in front of their abbreviated name. So if this is the case, rather than a finishing position being displayed, I need the cell to display "DNF".

The problem I am having currently, is that I am only able to get the SEARCH part of the formula to apply to one cell. The formula is as follows:

=IF(ISNUMBER(SEARCH("x",'ResultsS8 '!C18)),"DNF",(MATCH("*"&$A$2:$A$24&"*",'ResultsS8 '!C:C,0))-1)

The MATCH part is working correctly, and the "DNF" displays when it should (if I remove the "x" from the front of the abbreviated name on the original sheet, the correct number displays on the new sheet). But I'm only able to get it working when referencing one cell at the moment (C18 in this instance). Am I able to nest the correctly working MATCH function within the SEARCH function? Or is there a better way of getting the same end result?

Many thanks in advance!

 

[kweaver]

Welcome to Mr. Excel

If you use XL2BB to show your sample data and results you're looking for, you stand a better chance of someone replying. It's difficult to recreate what your sheet looks like without data.

 

[jamieraitt]

Hello,

First post in here and new to Excel in general, so go easy! Also, if there is a simpler method of doing this then please tell me!

I have been given a spreadsheet which contains the finishing positions of several races in a season. Unfortunately, it's not that easy to do any analysis on in the current format, which is:

BAH MON HUN etc...
1st GLY SWF SWF
2nd NAG JEF BLI
3rd WHI BLI LAW
etc...

I'm trying to get it into a format like this:

BAH MON HUN
BLI 11 3 2
GLY 1 12 DNF
JEF 8 2 11

On the sheet I have been given, if a player has a DNF (Did Not Finish), they have an "x" in front of their abbreviated name. So if this is the case, rather than a finishing position being displayed, I need the cell to display "DNF".

The problem I am having currently, is that I am only able to get the SEARCH part of the formula to apply to one cell. The formula is as follows:

=IF(ISNUMBER(SEARCH("x",'ResultsS8 '!C18)),"DNF",(MATCH("*"&$A$2:$A$24&"*",'ResultsS8 '!C:C,0))-1)

The MATCH part is working correctly, and the "DNF" displays when it should (if I remove the "x" from the front of the abbreviated name on the original sheet, the correct number displays on the new sheet). But I'm only able to get it working when referencing one cell at the moment (C18 in this instance). Am I able to nest the correctly working MATCH function within the SEARCH function? Or is there a better way of getting the same end result?

Many thanks in advance!

GPR_Current - Jamie.xlsx
	A	B	C	D
1	Position	Bahrain	Monza	Hungary
2	1	GLY	SWF	SWF
3	2	NAG	JEF	BLI
4	3	WHI	BLI	LAW
5	4	RAK	RAI	DOO
6	5	SWF	LAW	EAR
7	6	LAW	RAK	WIL
8	7	RAI	WIL	WHI
9	8	JEF	HWK	QAT
10	9	WIL	TOW	RAK
11	10	HWK	WHI	RAI
12	11	BLI	NAG	JEF
13	12	DOO	GLY	GRE_
14	13	EAR	EAR	xTOW
15	14	QAT	rERA	xrMOR
16	15	GRE	GRE
17	16	rERA	QAT_
18	17	xTOW	xDOO
19	18
20	19
ResultsS8

 

[jamieraitt]

GPR_Current - Jamie.xlsx
	A	B	C	D
1	Driver Name	Bahrain	Monza	Hungary
2	BEE	#N/A	#N/A	#N/A
3	BLI	11	3	2
4	BUL	#N/A	#N/A	#N/A
5	DOO	12	DNF	4
6	EAR	13	13	5
7	GLY	1	12	#N/A
8	GRE	15	15	12
9	HWK	10	8	#N/A
10	HWT	#N/A	#N/A	#N/A
11	JEF	8	2	11
12	LAW	6	5	3
13	LIN	#N/A	#N/A	#N/A
14	MRV	#N/A	#N/A	#N/A
15	MOR	#N/A	#N/A	14
16	NAG	2	11	#N/A
17	QAT	14	16	8
18	RAI	0	4	10
19	RAK	4	6	9
20	SWF	5	1	1
21	TOW	17	9	13
22	WHI	3	10	7
23	WIL	9	7	6
24	WOU	#N/A	#N/A	#N/A
New Results Sheet

Cell Formulas
Range	Formula
B6:D24,B5,D2:D5,B2:C4	B2	=MATCH("*"&$A$2:$A$24&"*",'ResultsS8 '!B:B,0)-1
C5	C5	=IF(ISNUMBER(SEARCH("x",'ResultsS8 '!C18)),"DNF",(MATCH("*"&$A$2:$A$24&"*",'ResultsS8 '!C:C,0))-1)

 

[jamieraitt]

Welcome to Mr. Excel

If you use XL2BB to show your sample data and results you're looking for, you stand a better chance of someone replying. It's difficult to recreate what your sheet looks like without data.

Thanks very much! I've included them now

 

[kweaver]

How about this?

Book10
	A	B	C	D
1	Driver Name	Bahrain	Monza	Hungary
2	BEE	#N/A	#N/A	#N/A
3	BLI	11	3	2
4	BUL	#N/A	#N/A	#N/A
5	DOO	12	DNF	4
6	EAR	13	13	5
7	GLY	1	12	#N/A
8	GRE	15	15	#N/A
9	HWK	10	8	#N/A
10	HWT	#N/A	#N/A	#N/A
11	JEF	8	2	11
12	LAW	6	5	3
13	LIN	#N/A	#N/A	#N/A
14	MRV	#N/A	#N/A	#N/A
15	MOR	#N/A	#N/A	DNF
16	NAG	2	11	#N/A
17	QAT	14	#N/A	8
18	RAI	7	4	10
19	RAK	4	6	9
20	SWF	5	1	1
21	TOW	DNF	9	DNF
22	WHI	3	10	7
23	WIL	9	7	6
24	WOU	#N/A	#N/A	#N/A
Sheet2

Cell Formulas
Range	Formula
B2:D24	B2	=IF("x"=LEFT(INDEX('ResultsS8 '!B$2:B$18,MATCH("*"&$A2,'ResultsS8 '!B$2:B$18,0)),1),"DNF",MATCH($A2,'ResultsS8 '!B$2:B$18,0))

 

[kweaver]

Adjustment for a few of the quirky ones:

Book10
	A	B	C	D
1	Driver Name	Bahrain	Monza	Hungary
2	BEE	#N/A	#N/A	#N/A
3	BLI	11	3	2
4	BUL	#N/A	#N/A	#N/A
5	DOO	12	DNF	4
6	EAR	13	13	5
7	GLY	1	12	#N/A
8	GRE	15	15	12
9	HWK	10	8	#N/A
10	HWT	#N/A	#N/A	#N/A
11	JEF	8	2	11
12	LAW	6	5	3
13	LIN	#N/A	#N/A	#N/A
14	MRV	#N/A	#N/A	#N/A
15	MOR	#N/A	#N/A	DNF
16	NAG	2	11	#N/A
17	QAT	14	16	8
18	RAI	7	4	10
19	RAK	4	6	9
20	SWF	5	1	1
21	TOW	DNF	9	DNF
22	WHI	3	10	7
23	WIL	9	7	6
24	WOU	#N/A	#N/A	#N/A
25	ERA	16	14	#N/A
Sheet2

Cell Formulas
Range	Formula
B2:D25	B2	=IF("x"=LEFT(INDEX('ResultsS8 '!B$2:B$18,MATCH("*"&$A2&"*",'ResultsS8 '!B$2:B$18,0)),1),"DNF",MATCH("*"&$A2&"*","*"&'ResultsS8 '!B$2:B$18,0))

 

[Peter_SSs]

Welcome to the MrExcel board!

1. What does the "r" prefix mean (retired?) and how should that be treated when assessing the position?
2. What does the "xr" prefix mean and how should it be treated when assessing the position?
3. What does the "_" suffix mean and how should it be treated when assessing the position?

 

[jamieraitt]

Adjustment for a few of the quirky ones:

Book10
	A	B	C	D
1	Driver Name	Bahrain	Monza	Hungary
2	BEE	#N/A	#N/A	#N/A
3	BLI	11	3	2
4	BUL	#N/A	#N/A	#N/A
5	DOO	12	DNF	4
6	EAR	13	13	5
7	GLY	1	12	#N/A
8	GRE	15	15	12
9	HWK	10	8	#N/A
10	HWT	#N/A	#N/A	#N/A
11	JEF	8	2	11
12	LAW	6	5	3
13	LIN	#N/A	#N/A	#N/A
14	MRV	#N/A	#N/A	#N/A
15	MOR	#N/A	#N/A	DNF
16	NAG	2	11	#N/A
17	QAT	14	16	8
18	RAI	7	4	10
19	RAK	4	6	9
20	SWF	5	1	1
21	TOW	DNF	9	DNF
22	WHI	3	10	7
23	WIL	9	7	6
24	WOU	#N/A	#N/A	#N/A
25	ERA	16	14	#N/A
Sheet2

Cell Formulas
Range	Formula
B2:D25	B2	=IF("x"=LEFT(INDEX('ResultsS8 '!B$2:B$18,MATCH("*"&$A2&"*",'ResultsS8 '!B$2:B$18,0)),1),"DNF",MATCH("*"&$A2&"*","*"&'ResultsS8 '!B$2:B$18,0))

How about this?

Book10
	A	B	C	D
1	Driver Name	Bahrain	Monza	Hungary
2	BEE	#N/A	#N/A	#N/A
3	BLI	11	3	2
4	BUL	#N/A	#N/A	#N/A
5	DOO	12	DNF	4
6	EAR	13	13	5
7	GLY	1	12	#N/A
8	GRE	15	15	#N/A
9	HWK	10	8	#N/A
10	HWT	#N/A	#N/A	#N/A
11	JEF	8	2	11
12	LAW	6	5	3
13	LIN	#N/A	#N/A	#N/A
14	MRV	#N/A	#N/A	#N/A
15	MOR	#N/A	#N/A	DNF
16	NAG	2	11	#N/A
17	QAT	14	#N/A	8
18	RAI	7	4	10
19	RAK	4	6	9
20	SWF	5	1	1
21	TOW	DNF	9	DNF
22	WHI	3	10	7
23	WIL	9	7	6
24	WOU	#N/A	#N/A	#N/A
Sheet2

Cell Formulas
Range	Formula
B2:D24	B2	=IF("x"=LEFT(INDEX('ResultsS8 '!B$2:B$18,MATCH("*"&$A2,'ResultsS8 '!B$2:B$18,0)),1),"DNF",MATCH($A2,'ResultsS8 '!B$2:B$18,0))

This one worked perfectly! When I used the second one, the numbers came up as "VALUE!" (would that be because of the &"*" after the A2 cell reference?), but I've checked this one in various places across the whole season and it all seems great. Thank you so much!

 

[jamieraitt]

Welcome to the MrExcel board!

1. What does the "r" prefix mean (retired?) and how should that be treated when assessing the position?
2. What does the "xr" prefix mean and how should it be treated when assessing the position?
3. What does the "_" suffix mean and how should it be treated when assessing the position?

Hi Peter

1. The "r" prefix means "reserve". There were a few stand-ins throughout the season. This doesn't affect any points awarded for finishing position.
2. The "x" means the driver Did Not Finish, and the "r" is as stated above. They have just been lumped together in some instances.
3. The "_" suffix means that the driver had been lapped in the race. This doesn't affect a driver's points for that race, but it does affect the points a team can score. If both drivers finish on the lead lap, the team gets an extra 5 points. Whilst I'm not concerned with this at the moment as I'm only looking at individual performances, this might be something I need to explore later!