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

dangorka

New Member
Joined
Feb 23, 2018
Messages
11
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
606
Office Version
2016
Platform
Windows
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?

SochauxCaen5Row=6
Paris FCSochaux6Row=11
SochauxAJ Auxerre48Row=29
LorientSochaux69Row=35
SochauxNancy
412Row=41
Le MansSochaux14Row=52
SochauxValenciennes3Row=68

<tbody>
</tbody>
 

dangorka

New Member
Joined
Feb 23, 2018
Messages
11
That's embarassing! You are completely correct, I made a mistake in the sheet
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
606
Office Version
2016
Platform
Windows
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.

ABCDLMNO
1Chambly FCValenciennes1Sochaux
2Rodez AveyronAJ Auxerre4SochauxValenciennes3
3GuingampGrenoble5Le MansSochaux14
4AjaccioLe Havre12SochauxNancy412
5ClermontChateauroux4LorientSochaux69
6SochauxCaen55
7NancyOrleans US 456

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Arkusz1

Worksheet Formulas
CellFormula
D1
and
down
=IF(OR(A1=$O$1,B1=$O$1),C1,"")
M2
and down
=IFERROR(INDEX($A$1:$A$70,AGGREGATE(14,6,ROW($A$1:$A$70)/($D$1:$D$70<>""),ROW()-1)),"")
N2
and down
=IFERROR(INDEX($B$1:$B$70,AGGREGATE(14,6,ROW($A$1:$A$70)/($D$1:$D$70<>""),ROW()-1)),"")
O2
and down
=IFERROR(INDEX($D$1:$D$70,AGGREGATE(14,6,ROW($A$1:$A$70)/($D$1:$D$70<>""),ROW()-1)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,419
Office Version
365
Platform
Windows
@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
ABCO
1Chambly FCValenciennes1Sochaux
2Rodez AveyronAJ Auxerre43
3GuingampGrenoble514
4AjaccioLe Havre12412
5ClermontChateauroux469
6SochauxCaen5
7NancyOrleans US 456
28NancyLorient66
29SochauxAJ Auxerre48
30ClermontLens53
31ValenciennesRodez Aveyron556
32CaenChambly FC63
33GrenobleTroyes41
34ChateaurouxAjaccio14
35LorientSochaux69
36NancyLe Mans67
37Orleans US 45Clermont6
38Paris FCChamois Niortais6
39LensLe Havre67
40AJ AuxerreGuingamp55
41SochauxNancy412
42Le MansLorient53
43AjaccioParis FC97
47Chambly FCChateauroux335
48Rodez AveyronOrleans US 455335
49TroyesLens6
50GuingampValenciennes67
51ChateaurouxTroyes6
52Le MansSochaux14
53CaenLe Havre67
65GuingampNancy1
66AjaccioOrleans US 4524
67Le HavreParis FC55
68SochauxValenciennes3
69ClermontLorient4
70LensChateauroux9
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
ABCDO
1Chambly FCValenciennes1
 
Sochaux
2Rodez AveyronAJ Auxerre43
3GuingampGrenoble514
4AjaccioLe Havre12412
5ClermontChateauroux469
6SochauxCaen51
7NancyOrleans US 456
8Chamois NiortaisTroyes66
9Le MansLens42
10LorientParis FC45
11Paris FCSochaux62
12Orleans US 45Chambly FC6
Arkusz1 (3)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,484
Messages
5,444,747
Members
405,299
Latest member
rcurtin

This Week's Hot Topics

Top