find first occurrence of item after something else

[palaeontology]

I have the following table of sports fixtures ...

I'm not permitted to edit the way the data is displayed, so I can't add columns to it, nor can I rename a team identifier, for example ... the 8A team in Touch Football ... I can't rename it 'Touch Football 8A'.

So, I'd like to have a way of finding (by formula) where a particular team is, for example, I'd like to find the location of the 8A team in Touch Football.

I'm thinking ... is there a way to find the first occurrence of the name '8A' after the word 'Touch Football' has occurred.

My spreadsheet that I'm using to strip information from the above spreadsheet is seen below ...

So, for example, under the title 'Opponent' I'd like to have a formula that finds the first occurrence of each of those team names (eg: 7A, 7B, 8A, 8B etc etc) after the word 'Touch Football'

I know how to find the nth occurrence of an item, but not the first occurrence after a specific other item has occurred.

Can anyone help with this ?

 

[Saba Sabaratnam]

Hi,

I hope that the following formula to demonstrate how to get a range between two values

=INDIRECT("A"&MATCH("TOUCH FOOTBALL",$A:$A,0)+1&":A"&MATCH("FOOTBALL - SOCCER",$A:$A,0)-1)

To pick unique values (I think that you know how to do it), you can use the array formula

=IFERROR(INDEX(INDIRECT("A"&MATCH("TOUCH FOOTBALL",$A:$A,0)+1&":A"&MATCH("FOOTBALL - SOCCER",$A:$A,0)-1),MATCH(0,COUNTIFS($E$5:E5,INDIRECT("A"&MATCH("TOUCH FOOTBALL",$A:$A,0)+1&":A"&MATCH("FOOTBALL - SOCCER",$A:$A,0)-1)),0),1),"")


My spreadsheet layout is presented below.

Kind regards

Saba

 

[Peter_SSs]

I'd like to find the location of the 8A team in Touch Football.

I'm thinking ... is there a way to find the first occurrence of the name '8A' after the word 'Touch Football' has occurred.

1. Can we be certain that there will be an 8A in the Touch Football section?
2. What do you mean by "location"? Is it a particular column of the table?
3. Can we have the sample data again in a form we can copy to test/demonstrate? XL2BB

 

[jasonb75]

This is a duplicate thread guys, I have flagged it to mods. Index Match query

 

[steve the fish]

Something like this?

=INDEX($B$1:$B$1000,MATCH(1,INDEX((($A$1:$A$1000="9a")*(ROW($A$1:$A$1000)>MATCH("touch football",$A$1:$A$1000,0))),0),0))

Where the 9a and touch football can be cell references.

 

[Snakehips]

This is my take on it....

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
12	BASKETBALL
13	Opens	St Margaret's		Ven 1	1	5:00						Team	Opponent	Venue	Field	Start
14	Senior A	St Margaret's		Ven 2	1	4:00						7A	St Margaret's	Ven 10	1	4:00
15	10A	St Margaret's		Ven 3	2	5:00						7B
16	10B	St Margaret's		Ven 4	4	5:00						8A	St Margaret's	Ven 8	4	4:00
17	9A	St Margaret's		Ven 5	3	5:00						8B
18	9B	St Margaret's		Ven 6	2	4:00						9A	St Margaret's	Ven 6	3	4:00
19	8A	St Margaret's		Ven 7	4	4:00						9B	St Margaret's	Ven 7	2	4:50
20	7A	St Margaret's		Ven 8	3	4:00						10A	St Margaret's	Ven 4	3	4:50
21	TOUCH FOOTBALL											10B	St Margaret's	Ven 5	4	4:50
22	Opens	St Margaret's		Ven 1	3	5:40						Senior A	St Margaret's	Ven 2	3	6:20
23	Senior A	St Margaret's		Ven 2	3	6:20						Senior B	St Margaret's	Ven 3	4	5:40
24	Senior B	St Margaret's		Ven 3	4	5:40						Opens	St Margaret's	Ven 1	3	5:40
25	10A	St Margaret's		Ven 4	3	4:50
26	10B	St Margaret's		Ven 5	4	4:50
27	9A	St Margaret's		Ven 6	3	4:00
28	9B	St Margaret's		Ven 7	2	4:50
29	8A	St Margaret's		Ven 8	4	4:00
30	8C (8B Draw)	St Margaret's		Ven 9	1	4:50
31	7A	St Margaret's		Ven 10	1	4:00
32	7C (7B Draw)	St Margaret's		Ven 11	2	4:00
33	Senior C	St Margaret's		Ven 12	4	6:20
Sheet6

Cell Formulas
Range	Formula
M14:M24	M14	=IFERROR(INDEX($B$1:$F$100,MATCH($L14,OFFSET($A$1:$A$100,MATCH("TOUCH FOOTBALL",$A$1:$A$100,0),0),0)+MATCH("TOUCH FOOTBALL",$A$1:$A$100,0),1),"")
N14:N24	N14	=IFERROR(INDEX($B$1:$F$100,MATCH($L14,OFFSET($A$1:$A$100,MATCH("TOUCH FOOTBALL",$A$1:$A$100,0),0),0)+MATCH("TOUCH FOOTBALL",$A$1:$A$100,0),3),"")
O14:O24	O14	=IFERROR(INDEX($B$1:$F$100,MATCH($L14,OFFSET($A$1:$A$100,MATCH("TOUCH FOOTBALL",$A$1:$A$100,0),0),0)+MATCH("TOUCH FOOTBALL",$A$1:$A$100,0),4),"")
P14:P24	P14	=IFERROR(INDEX($B$1:$F$100,MATCH($L14,OFFSET($A$1:$A$100,MATCH("TOUCH FOOTBALL",$A$1:$A$100,0),0),0)+MATCH("TOUCH FOOTBALL",$A$1:$A$100,0),5),"")

The appropriately equivalent formula of M14. drag across. > edit the column parameter of the INDEX ( as in 1,3,4 or 5) > Then drag down.

Hope that helps