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!

 

[jamieraitt]

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!

Sorry, I'd accidentally included both. Just to be clear, your first solution worked a charm, the second one showed "VALUES!"

 

[kweaver]

As you saw from my post regarding the 2nd version, I didn't get any #Value! error. Did you change the input data? if so, I'd be happy to look at it again if you post it.

 

[Peter_SSs]

your first solution worked a charm,

Yet you said ..

The "_" suffix means that the driver had been lapped in the race. This doesn't affect a driver's points for that race

and post #4 shows "16" for QAT in Monza and "12" for GRE in Hungary but post #6 (first solution) shows #N/A for both? (I believe that's why @kweaver posted a revised version)

Further, since driver MOR is listed in the 'New Results Sheet' table, if they had finished the race in Hungary, shouldn't they get a position in the results table (I assume position 13 because if they had finished they would have appeared above xTOW in ResultsS8?). kweaver's first solution would give them #N/A but second solution gives them a position

I tried a slightly different approach. Note that I have changed a couple of values (highlighted) in ResultsS8 to test what I think you would want in relation to the previous paragraph..
I also guessed that you would not really want all those #N/A values, though they are easily restored from my formula if required.

jamieraitt.xlsm
	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	rMOR
15	14	QAT	rERA	xTOW
16	15	GRE	GRE
17	16	rERA	QAT_
18	17	xTOW	xDOO
19	18
20	19
ResultsS8

jamieraitt.xlsm
	A	B	C	D
1	Driver Name	Bahrain	Monza	Hungary
2	BEE
3	BLI	11	3	2
4	BUL
5	DOO	12	DNF	4
6	EAR	13	13	5
7	GLY	1	12
8	GRE	15	15	12
9	HWK	10	8
10	HWT
11	JEF	8	2	11
12	LAW	6	5	3
13	LIN
14	MRV
15	MOR			13
16	NAG	2	11
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
New Results Sheet

Cell Formulas
Range	Formula
B2:D24	B2	=IF(COUNTIF('ResultsS8 '!B$2:B$20,"x*"&$A2),"DNF",IFNA(MATCH("*"&$A2&"*",'ResultsS8 '!B$2:B$20,0),""))

 

[jamieraitt]

Yet you said ..

and post #4 shows "16" for QAT in Monza and "12" for GRE in Hungary but post #6 (first solution) shows #N/A for both? (I believe that's why @kweaver posted a revised version)

Further, since driver MOR is listed in the 'New Results Sheet' table, if they had finished the race in Hungary, shouldn't they get a position in the results table (I assume position 13 because if they had finished they would have appeared above xTOW in ResultsS8?). kweaver's first solution would give them #N/A but second solution gives them a position

I tried a slightly different approach. Note that I have changed a couple of values (highlighted) in ResultsS8 to test what I think you would want in relation to the previous paragraph..
I also guessed that you would not really want all those #N/A values, though they are easily restored from my formula if required.

jamieraitt.xlsm
	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	rMOR
15	14	QAT	rERA	xTOW
16	15	GRE	GRE
17	16	rERA	QAT_
18	17	xTOW	xDOO
19	18
20	19
ResultsS8

jamieraitt.xlsm
	A	B	C	D
1	Driver Name	Bahrain	Monza	Hungary
2	BEE
3	BLI	11	3	2
4	BUL
5	DOO	12	DNF	4
6	EAR	13	13	5
7	GLY	1	12
8	GRE	15	15	12
9	HWK	10	8
10	HWT
11	JEF	8	2	11
12	LAW	6	5	3
13	LIN
14	MRV
15	MOR			13
16	NAG	2	11
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
New Results Sheet

Cell Formulas
Range	Formula
B2:D24	B2	=IF(COUNTIF('ResultsS8 '!B$2:B$20,"x*"&$A2),"DNF",IFNA(MATCH("*"&$A2&"*",'ResultsS8 '!B$2:B$20,0),""))

Ahh yes, I see what you mean. Yes they would still need a position and that wasn't showing correctly before. Thank you very much for this one. It has given positions for those with prefixes and suffixes, which is useful to see even if the points don't quite work in the same way. And I wasn't even aware of an IFNA function (like I said previously, very new to all this!) so thank you for introducing me to that as well.

I've tried to use all this in a Pivot Table to then make a Chart from that can display a driver's progress throughout the season, and I've had a couple of issues.

1. I'm having to put all the races into the Values Section individually, as that data is all columns. Not the end of the world, but it would be helpful if I could just put the whole season's races in there, then filter out if needs be (as I can with all the Driver Names). Is there a way of doing this?

2. Any driver that has a DNF or blank space (didn't attend) in any race displays a "0" in the Pivot Table, therefore sits above 1 on the chart, making many drivers appear much better than they actually were! Is there a way of either making sure a "0" appears at the bottom of my chart, or changing the "0" to another value?

I'm having issues with my XL2BB disappearing so I'm not able to paste that in there currently. I have taken a screenshot of the start of what I'm doing though if that helps.

 

[Peter_SSs]

I'm having issues with my XL2BB disappearing

Near the bottom of the XL2BB instructions page there is a section headed "Known XL2BB issues" and included there is a link to this page.

 

[jamieraitt]

Near the bottom of the XL2BB instructions page there is a section headed "Known XL2BB issues" and included there is a link to this page.

Thanks Peter, all working again now!