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

#### dangorka

##### New Member
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

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:

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

##### Well-known Member
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?

 Sochaux Caen 5 Row=6 Paris FC Sochaux 6 Row=11 Sochaux AJ Auxerre 48 Row=29 Lorient Sochaux 69 Row=35 Sochaux Nancy 412 Row=41 Le Mans Sochaux 14 Row=52 Sochaux Valenciennes 3 Row=68

<tbody>
</tbody>

#### dangorka

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

##### Well-known Member
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

</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)),"")

</tbody>

<tbody>
</tbody>

#### Peter_SSs

##### MrExcel MVP, Moderator
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:

Replies
11
Views
460
Replies
1
Views
42
Replies
1
Views
107
Replies
5
Views
186
Replies
10
Views
295

1,127,861
Messages
5,627,308
Members
416,239
Latest member
Counselor85027

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back