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

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).

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,395
Office Version
  1. 2016
Platform
  1. 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
1,395
Office Version
  1. 2016
Platform
  1. 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
48,400
Office Version
  1. 365
Platform
  1. 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,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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top