INDEX/AGGREGATE/ROW Formula

James8761

Board Regular
Joined
Apr 24, 2012
Messages
156
Office Version
  1. 365
Platform
  1. Windows
I had this formula in a spreadsheet where I was kindly helped from here. I run a running club and I am trying to improve my spreadsheet.

The formula I had was =IFERROR(INDEX(Runners!$P$2:$P$3843,AGGREGATE(15,6,(ROW(Runners!$P$2:$P$3843)-ROW(!$B$2)+1)/(Runners!$A$2:$A$3843='Race 1'!$B339), COLUMNS($O339:O339))),"")

Please ignore the tab names, it is the formula I am struggling with. The formula above gives me the answer 56. Which is correct, no problem with that, but I am trying to add in text of " - " and also another column (their finishing position). So the full answer would be 56 - 1 etc.

I have copied some dummy data below: The data covers the range A1:P30, with the headers being on Row 1. This tab is called Data.

NameNumberPositionTotalDateConditionsQuickCourseDistanceAttendanceTrackClassificationsRace TypeAge RangePaidRating
Turner
55​
5​
25​
05/12/2019​
ColdYesBirmingham10kZeroQuickMixedMixedAnyY
81​
Turner
64​
4​
25​
18/11/2019​
WindyYesManchester8kZeroSlowMixedMixedAnyY
82​
Davis
33​
7​
25​
18/11/2019​
WindyYesManchester8kZeroSlowMixedMixedAnyY
65​
Turner
62​
3​
25​
07/11/2019​
RainYesEdinburgh10kZeroSlowMenMenAnyY
85​
Davis
59​
2​
25​
07/11/2019​
RainYesEdinburgh10kZeroSlowMenMenAnyY
88​
Turner
61​
7​
25​
16/10/2019​
RainNoBirmingham5kZeroSlowMenMenAnyY
87​
Turner
67​
1​
25​
19/09/2019​
SunnyYesExeter10kZeroMediumMenMenAnyY
74​
Davis
69​
2​
25​
19/09/2019​
SunnyYesExeter10kZeroMediumMenMenAnyY
71​
Turner
62​
4​
25​
10/09/2019​
SunnyYesBirmingham8kZeroGoodMixedMixedAnyY
52​
Turner
71​
1​
30​
26/08/2019​
RainYesManchester10kZeroSlowMixedMixedAnyY
75​
Davis
98​
2​
30​
26/08/2019​
RainYesManchester10kZeroSlowMixedMixedAnyY
65​
Turner
53​
6​
40​
06/08/2019​
WindyYesLondon10kZeroSlowMixedMixedAnyY
53​
Turner
58​
6​
40​
24/07/2019​
HotYesCardiff10kZeroGoodMixedMixedAnyY
55​
Turner
57​
9​
40​
17/06/2019​
RainYesCardiff5kZeroSlowMenMenAnyY
58​
Turner
43​
0​
40​
10/06/2019​
HotYesManchester10kZeroQuickMenMenAnyY
58​
Davis
48​
5​
40​
10/06/2019​
HotYesManchester10kZeroQuickMenMenAnyY
68​
Turner
64​
4​
40​
28/05/2019​
RainYesLondon10kZeroSlowMixedMixedAnyY
76​
Davis
75​
2​
45​
28/05/2019​
RainYesLondon10kZeroSlowMixedMixedAnyY
92​
Turner
59​
6​
40​
11/05/2019​
WindyYesBirmingham8kZeroSlowMixedMixedAnyY
60​
Turner
55​
4​
40​
26/11/2018​
ColdYesManchester10kZeroSlowMixedMixedAnyY
70​
Turner
36​
4​
40​
08/11/2018​
ColdYesEdinburgh6fZeroSlowMixedMixedAnyY
70​
Davis
25​
3​
40​
08/11/2018​
ColdYesEdinburgh6fZeroSlowMixedMixedAnyY
85​
Turner
59​
2​
40​
04/11/2018​
ColdNoEdinburgh5fZeroSlowMixedMixedAnyY
70​
Turner
40​
6​
40​
24/08/2018​
SunnyNoBirmingham10kZeroQuickMenMenAnyY
71​
Davis
55​
2​
11​
24/08/2018​
SunnyNoBirmingham10kZeroQuickMenMenAnyY
84​
Turner
61​
2​
50​
11/08/2018​
HotYesCardiff8kZeroQuickMenMenU-25Y
65​
Davis
12​
9​
46​
11/08/2018​
HotYesCardiff8kZeroQuickMenMenU-25Y
33​
Turner
44​
7​
50​
30/07/2018​
HotNoBirmingham10kZeroQuickMenMenU-25Y
62​
Turner
50​
4​
50​
26/05/2018​
RainNoManchester5kZeroSlowMenMenU-25Y
64​

On the tab next to it, named Summary. I have put headings such as Name, Conditions, Course, Distance and Track. Next to those headings I would type the persons naem, the conditions, today's course, the distance and the track.

This would then bring back the most recent results (most recent date first) and then their finishing position. The columns I am trying to capture are 'Rating' and 'Position'. In the example below. I have typed in Turner, Rain, Birmingham, 10k and Slow.

It then looks at the last 5 occurrences of these conditions and brings back the Rating and the Position, results below 85 - 3 87 - 7 75 -1 58 - 9 76 - 4 for the last time "Turner" ran in Conditions that were "Rain" plus a - (with a space either side of the -) then their finishing position. In the screen dump below the merge formatting hasn't worked. There should be 5 cells each for Condition, Course, Distance and Track. I have tried using the xl2bb add in, I hope this has worked.

Any help greatly appreciated.

NameTurnerConditionsRainCourseBirminghamDistance10kTrackSlow
ConditionCourseDistanceTrack
85 - 387 - 775 - 158 - 976 - 481 - 587 - 752 - 460 - 671 - 681 - 585 - 374 - 175 - 153 - 682 - 485 - 387 - 775 - 153 - 6



Runner Data.xlsx
ABCDEFGHIJKLMNOP
1NameNumberPositionTotalDateConditionsQuickCourseDistanceAttendanceTrackClassificationsRace TypeAge RangePaidRating
2Turner5552505/12/2019ColdYesBirmingham10kZeroQuickMixedMixedAnyY81
3Turner6442518/11/2019WindyYesManchester8kZeroSlowMixedMixedAnyY82
4Davis3372518/11/2019WindyYesManchester8kZeroSlowMixedMixedAnyY65
5Turner6232507/11/2019RainYesEdinburgh10kZeroSlowMenMenAnyY85
6Davis5922507/11/2019RainYesEdinburgh10kZeroSlowMenMenAnyY88
7Turner6172516/10/2019RainNoBirmingham5kZeroSlowMenMenAnyY87
8Turner6712519/09/2019SunnyYesExeter10kZeroMediumMenMenAnyY74
9Davis6922519/09/2019SunnyYesExeter10kZeroMediumMenMenAnyY71
10Turner6242510/09/2019SunnyYesBirmingham8kZeroGoodMixedMixedAnyY52
11Turner7113026/08/2019RainYesManchester10kZeroSlowMixedMixedAnyY75
12Davis9823026/08/2019RainYesManchester10kZeroSlowMixedMixedAnyY65
13Turner5364006/08/2019WindyYesLondon10kZeroSlowMixedMixedAnyY53
14Turner5864024/07/2019HotYesCardiff10kZeroGoodMixedMixedAnyY55
15Turner5794017/06/2019RainYesCardiff5kZeroSlowMenMenAnyY58
16Turner4304010/06/2019HotYesManchester10kZeroQuickMenMenAnyY58
17Davis4854010/06/2019HotYesManchester10kZeroQuickMenMenAnyY68
18Turner6444028/05/2019RainYesLondon10kZeroSlowMixedMixedAnyY76
19Davis7524528/05/2019RainYesLondon10kZeroSlowMixedMixedAnyY92
20Turner5964011/05/2019WindyYesBirmingham8kZeroSlowMixedMixedAnyY60
21Turner5544026/11/2018ColdYesManchester10kZeroSlowMixedMixedAnyY70
22Turner3644008/11/2018ColdYesEdinburgh6fZeroSlowMixedMixedAnyY70
23Davis2534008/11/2018ColdYesEdinburgh6fZeroSlowMixedMixedAnyY85
24Turner5924004/11/2018ColdNoEdinburgh5fZeroSlowMixedMixedAnyY70
25Turner4064024/08/2018SunnyNoBirmingham10kZeroQuickMenMenAnyY71
26Davis5521124/08/2018SunnyNoBirmingham10kZeroQuickMenMenAnyY84
27Turner6125011/08/2018HotYesCardiff8kZeroQuickMenMenU-25Y65
28Davis1294611/08/2018HotYesCardiff8kZeroQuickMenMenU-25Y33
29Turner4475030/07/2018HotNoBirmingham10kZeroQuickMenMenU-25Y62
30Turner5045026/05/2018RainNoManchester5kZeroSlowMenMenU-25Y64
Data
Cell Formulas
RangeFormula
B6B6=B24


Runner Data.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2
3NameTurnerConditionsRainCourseBirminghamDistance10kTrackSlow
4
5
6
7ConditionCourseDistanceTrack
885 - 387 - 775 - 158 - 976 - 481 - 587 - 752 - 460 - 671 - 681 - 585 - 374 - 175 - 153 - 682 - 485 - 387 - 775 - 153 - 6
Summary
Cell Formulas
RangeFormula
B8:U8B8=B13&" - "&B14
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
How about
Excel Formula:
=INDEX(Data!$P$2:$P$30,AGGREGATE(15,6,(ROW(Data!$P$2:$P$30)-ROW(Data!$P$2)+1)/(Data!$A$2:$A$30=$C$3)/(Data!$F$2:$F$30=$E$3),COLUMNS($B8:B8)))&" - "&INDEX(Data!$C$2:$C$30,AGGREGATE(15,6,(ROW(Data!$P$2:$P$30)-ROW(Data!$P$2)+1)/(Data!$A$2:$A$30=$C$3)/(Data!$F$2:$F$30=$E$3),COLUMNS($B8:B8)))
 
Upvote 0
Solution
How about
Excel Formula:
=INDEX(Data!$P$2:$P$30,AGGREGATE(15,6,(ROW(Data!$P$2:$P$30)-ROW(Data!$P$2)+1)/(Data!$A$2:$A$30=$C$3)/(Data!$F$2:$F$30=$E$3),COLUMNS($B8:B8)))&" - "&INDEX(Data!$C$2:$C$30,AGGREGATE(15,6,(ROW(Data!$P$2:$P$30)-ROW(Data!$P$2)+1)/(Data!$A$2:$A$30=$C$3)/(Data!$F$2:$F$30=$E$3),COLUMNS($B8:B8)))

Wow, that's absolutely fantastic. Just been sitting here trying it all out. That is exactly what I needed. Thank you so much :)(y)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

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
Back
Top