Help with Index Match formula finding last 4 occurences of the name

[dangorka]

Hi guys,

I have a simple Excel sheet which contains data in two sheets (basically a football league schedule)

Data is contained in column A (home team) and column B (away team for each match)

In column O I manually put the name of the team and formula has to find last four times when following team played a game (no matter if they were home or away team) and match the value from column C from last occurence being listed in cell O2, one before last occurence in cell O3 etc.

I attached the spreadsheet with values which must be found by formula in range O2:O5

https://docs.google.com/spreadsheets/d/1-Waq9s8Xd8bMLxHSuZgkTmkKvq2gXzYtGiVHyoK6FaQ/edit?usp=sharing

Any idea how to achieve that with formulas?
Thanks

Also posted here
https://www.excelforum.com/excel-fo...ast-4-occurences-of-the-name.html#post5197202

 

[Toadstool]

I'm not sure I understand. Your example has
Sochaux
3
14
412
6

...but I would expect
Sochaux
3
14
412
69

Am I misunderstanding?

[TABLE="class: grid, width: 391"]
<tbody>[TR]
[TD]Sochaux[/TD]
[TD]Caen[/TD]
[TD]5[/TD]
[TD]Row=6[/TD]
[/TR]
[TR]
[TD]Paris FC[/TD]
[TD]Sochaux[/TD]
[TD]6[/TD]
[TD]Row=11[/TD]
[/TR]
[TR]
[TD]Sochaux[/TD]
[TD]AJ Auxerre[/TD]
[TD]48[/TD]
[TD]Row=29[/TD]
[/TR]
[TR]
[TD]Lorient[/TD]
[TD]Sochaux[/TD]
[TD]69[/TD]
[TD]Row=35[/TD]
[/TR]
[TR]
[TD]Sochaux[/TD]
[TD]Nancy
[/TD]
[TD]412[/TD]
[TD]Row=41[/TD]
[/TR]
[TR]
[TD]Le Mans[/TD]
[TD]Sochaux[/TD]
[TD]14[/TD]
[TD]Row=52[/TD]
[/TR]
[TR]
[TD]Sochaux[/TD]
[TD]Valenciennes[/TD]
[TD]3[/TD]
[TD]Row=68[/TD]
[/TR]
</tbody>[/TABLE]

 

[dangorka]

That's embarassing! You are completely correct, I made a mistake in the sheet

 

[Toadstool]

I struggled with getting the last four if I must check either column A or B for the club name, so I had to add a worker column D.

	A	B	C	D	L	M	N	O
Chambly FC	Valenciennes
Rodez Aveyron	AJ Auxerre		Sochaux	Valenciennes
Guingamp	Grenoble		Le Mans	Sochaux
Ajaccio	Le Havre		Sochaux	Nancy
Clermont	Chateauroux		Lorient	Sochaux
Sochaux	Caen
Nancy	Orleans US 45

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Sochaux[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]412[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]69[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Arkusz1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1
and
down
[/TH]
[TD="align: left"]=IF(OR(A1=$O$1,B1=$O$1),C1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2
and down
[/TH]
[TD="align: left"]=IFERROR(INDEX($A$1:$A$70,AGGREGATE(14,6,ROW($A$1:$A$70)/($D$1:$D$70<>""),ROW()-1)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N2
and down
[/TH]
[TD="align: left"]=IFERROR(INDEX($B$1:$B$70,AGGREGATE(14,6,ROW($A$1:$A$70)/($D$1:$D$70<>""),ROW()-1)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O2
and down
[/TH]
[TD="align: left"]=IFERROR(INDEX($D$1:$D$70,AGGREGATE(14,6,ROW($A$1:$A$70)/($D$1:$D$70<>""),ROW()-1)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

@Toadstool
Just a comment on your column M, N, O formulas: If a user subsequently inserts any new rows at the top of the sheet, those formulas will no longer return the correct results.

@dangorka
Here is a way to get the results directly in O2:O5 without needing a helper column
Note that the yellow in A:B is just so I could easily see where the relevant entries were.
I have hidden some rows to make my post here a bit shorter.

Excel Workbook
	A	B	C	O
1	Chambly FC	Valenciennes	1	Sochaux
2	Rodez Aveyron	AJ Auxerre	4	3
3	Guingamp	Grenoble	5	14
4	Ajaccio	Le Havre	12	412
5	Clermont	Chateauroux	4	69
6	Sochaux	Caen	5
7	Nancy	Orleans US 45	6
28	Nancy	Lorient	66
29	Sochaux	AJ Auxerre	48
30	Clermont	Lens	53
31	Valenciennes	Rodez Aveyron	556
32	Caen	Chambly FC	63
33	Grenoble	Troyes	41
34	Chateauroux	Ajaccio	14
35	Lorient	Sochaux	69
36	Nancy	Le Mans	67
37	Orleans US 45	Clermont	6
38	Paris FC	Chamois Niortais	6
39	Lens	Le Havre	67
40	AJ Auxerre	Guingamp	55
41	Sochaux	Nancy	412
42	Le Mans	Lorient	53
43	Ajaccio	Paris FC	97
47	Chambly FC	Chateauroux	335
48	Rodez Aveyron	Orleans US 45	5335
49	Troyes	Lens	6
50	Guingamp	Valenciennes	67
51	Chateauroux	Troyes	6
52	Le Mans	Sochaux	14
53	Caen	Le Havre	67
65	Guingamp	Nancy	1
66	Ajaccio	Orleans US 45	24
67	Le Havre	Paris FC	55
68	Sochaux	Valenciennes	3
69	Clermont	Lorient	4
70	Lens	Chateauroux	9
Arkusz1

If you do like the idea of a helper column to keep the formulas a bit simpler, here is a variation of the earlier suggestion (part sheet only shown)

Excel Workbook
	A	B	C	D	O
1	Chambly FC	Valenciennes	1		Sochaux
2	Rodez Aveyron	AJ Auxerre	4		3
3	Guingamp	Grenoble	5		14
4	Ajaccio	Le Havre	12		412
5	Clermont	Chateauroux	4		69
6	Sochaux	Caen	5	1
7	Nancy	Orleans US 45	6
8	Chamois Niortais	Troyes	66
9	Le Mans	Lens	42
10	Lorient	Paris FC	45
11	Paris FC	Sochaux	6	2
12	Orleans US 45	Chambly FC	6
Arkusz1 (3)